purifying data

[d2c]

I have two customer lists. Column A has name (last,first). I need to be able
to compare the names from the entire column and eliminate the duplicates. I
cannot use EXACT because the column/rows do not line up. Example a2 may have
smith, john on one the first worksheet and a2 on the other wooksheet may have
johnson, bill. The second worksheet may not even have the name of smith,john.

 

[ShaneDevenshire]

Hi,

Of course there is a question as to what you mean by "duplicates". If the
item is found on both sheets it is considered a duplicate. In some
situations the second occurance is considered a duplicate while the first is
not. I will consider the first case.

You could mark the duplicates with conditional formatting. Let's say you
want to remove them. Assume that A1:A1000 on sheet1 needs to be compared
with A1:A1000 on sheet2 with titles in the top row, and suppose you want to
remove the duplicates from sheet1.

On sheet1 in cell B2 enter the following formula:

=COUNTIF(Sheet2!A$2:A$1000,A2)

1. Copy this formula down. All names found on the second sheet will return
a value of 1, all names that are not on the second sheet will return 0.
2. with your cursor in the data on sheet1 choose Data, Filter, AutoFilter
3. Open the filter in B1 and choose 1 all the records that are duplicates
witll be visible,
4. Select the visible cell and choose Edit, Delete Row
5. Remove the AutoFilter - choose Data, Filter, AutoFilter
6. Clear the formulas from column B

If names are not counted as duplicates unless the match cast then the
formula is more complicated but the process is the same:

=OR(EXACT(A2,Sheet2!A$2:A$1000))

This formula requires array entry - you must press Shift Ctrl Enter, not
Enter.