Forum Discussion
Finding Duplicates
I need to be able to compare two sets of numeric data, each column has very long numbers and I want to be able to identify the duplicates from each column. I have tried using conditional formatting and highlighting duplicates however it shows up virtually everything as a duplicate which is not the case. I suspect its something to do with the formatting of the cells. I have included a data sample, each column has around 4000+ cells.
Data Sample
1 Reply
Richwhite Being on a Mac your options are limited. I can think of three right now.
1) Append both lists into one column and add a column that contains the source. Then, apply conditional formatting to highlight duplicates;
2) Use a pivot table on the appended list;
3) Use VLOOKUP (or XLOOKUP if your Excel versions supports it). #N/A means no duplicate found, a number indicates that the number was found in the other list, thus, it's a duplicate.
All three are demonstrated in the attached file, though only using lists containing 5 numbers each.
