locked
how to get rid of duplicates by one column against another RRS feed

  • Question

  • hi all,

    I have a need to get rid of duplicates from one column against the other column, e.g., column A has 100 rows of data, column B has 50 rows, I want to compare A against B and find which data that don't appear in B and only show up in A. I'm still use Excel 2013.

    thanks,

    Sheldon

    Tuesday, October 27, 2020 2:30 PM

Answers

  • Let's say the data begin in row 2.

    Select A2:A100 (or however far the data go)

    On the Home tab of the ribbon, select Conditional Formatting > New Rule...

    Select 'Use a formula to determine which cells to format'.

    Enter the formula

    =ISERROR(MATCH(A2,$B$2:$B$50,0))

    Adjust the range $B$2:$B$50 as needed.

    Click Format...

    Activate the Fill tab.

    Select a color, for example red.

    Click OK, then click OK again.

    You can filter column A to show only the colored cells, or only the non-colored cells.


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    • Marked as answer by sesquipedalian Tuesday, October 27, 2020 6:13 PM
    Tuesday, October 27, 2020 2:39 PM

All replies

  • Let's say the data begin in row 2.

    Select A2:A100 (or however far the data go)

    On the Home tab of the ribbon, select Conditional Formatting > New Rule...

    Select 'Use a formula to determine which cells to format'.

    Enter the formula

    =ISERROR(MATCH(A2,$B$2:$B$50,0))

    Adjust the range $B$2:$B$50 as needed.

    Click Format...

    Activate the Fill tab.

    Select a color, for example red.

    Click OK, then click OK again.

    You can filter column A to show only the colored cells, or only the non-colored cells.


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    • Marked as answer by sesquipedalian Tuesday, October 27, 2020 6:13 PM
    Tuesday, October 27, 2020 2:39 PM
  • wonderful! it works.

    thank you a lot,

    Sheldon.

    Tuesday, October 27, 2020 6:17 PM