CODEWITHSUNDEEP
excel
joe
joe

Reputation: 1473

Finding unique values between 2 Excel columns

I have two simple columns in Excel below

ID       ID1
123      123
124      125
125      126
126

I was able to use the conditional formatting in excel as follow: 1. Highlight the 2 columns 2. Click on the conditional formatting 3. New rule 4. Select format only unique or duplicate values 5. Select unique under format all: 6. Select Format and click ok.

I can see that the steps above shows the highlighted value which is 124.

My question is, how can I filter out that 124 value from such a small sample above?

I have a columnA that has 50k records and columnB that has 48k records. I want to see or filter out the 2k records from columnA.

Upvotes: 2

Views: 20323

Answers (2)

xQbert
xQbert

Reputation: 35343

Here's one approach using match and a filter.

Enter =MATCH(A1,B:B,) in column c add a filter to row 1 and filter for column C values that are #N/A the values in column A which say #N/A in column C are not in column B. enter image description here

An alternative approach would be to move the column B under Column A and use COLUMN b to define the source ID, ID1... and then use a pivot table to show you both sets and what could be missing from each...

enter image description here

Notice how 5 is not IN ID while 13 is in ID but not in ID1. also notice that duplicates on Row 5 show up as well.

Upvotes: 2

Lakshya Goyal
Lakshya Goyal

Reputation: 360

One way of doing this (kind of manual) is to find the values which are duplicates (the ones that are highlighted). Then select both the columns and all the data.

Then go to: Home > Editing group> Sort & Filter > Filter

There should be small, down arrows on the column headers. Then you can un-check the duplicates.

EDIT

Thanks to xQbert for pointing out my mistake. Here is a way to solve this:

If possible, you can move the second column to another worksheet. Now use the following formula in a column next to the first column: =COUNTIF(Sheet2!A2:A5,Sheet1!A2:A5)

Just change the cells to the ones for your table. The first parameter is the second column (which you should have put in the new sheet). The second parameter is the first column which will be highlighted. This will put a '1' next to each value which is duplicated.

Then you can use conditional formatting to highlight the cells with a '1' next to them using this formula in the "New Rule":

=B2=1 That is the cell number of the first cell in the first column. It should be highlighted to the colour you set it to. To copy the formatting to the rest of the cells, click on the first cell B1. Then go Home > Format Painter. Drag the formatting to the entire column.

You can then use the filter to show only the cells with "No Fill"

You will have to do the same for the data in the new sheet.

This was a very 'hacky' solution but it's what is possible.

Upvotes: 0

Related Questions