Removing both duplicate AND original records? OR leaving only unique rows

**smohyee** · 04-18-2011, 06:42 PM

Hi all

So I've gone through a sheet of records for a mailing list, deleting about 5k records from a list of 15k. Now I've got the original list of 15k, and a 'cleaned' list with 10k names. What I want is a list of the 5k names I deleted.

I've tried copying the 15k list and the 10k list onto the same spreadsheet, so now roughly 20k of those 25k records are duplicates, and the remaining 5k are the ones I want. Now I want to remove the duplicate records as well as the originals that were duplicated.

I thought I was onto something with the Advanced Filter button, which has a checkbox called "unique records only". But apparently that also leaves one of the two duplicate records on display, just as the "remove duplicates" button does.

Any suggestions?

Edit: BTW, the COUNTIF function apparently only applies to cells, not entire rows. And I need the entire ROW to be matching (I have records where the name cell is identical, but address cells aren't, etc, and those need to be treated as entirely different records).

**tlafferty** · 04-18-2011, 06:46 PM

Can you post a sample of your workbook?

**tlafferty** · 04-18-2011, 07:31 PM

Assuming you have a unique record identifier and that you aren't relying on more than one piece of information to uniquely identify a record, try the following:

Place the two lists in the workbook.
Name the cleansed list cleansed
In your original list, search for the unique identifier in the cleansed list.

Let's say that your ID is in the A column in both lists.

In your original list, use this formula:
=ISERROR(VLOOKUP(A2,cleansed,1,false))

Filter for true - any true cells do not appear in your cleansed list and must have been deleted.

**smohyee** · 04-19-2011, 11:54 AM

tlafferty: Here's a sample of what I'm talking about. I didn't use the real data, obviously, and the actual lists I'm working with are much longer. But those columns represent the info that uniquely identifies each record.

To answer your second post, no, there isn't an identifier associated with each record (though that would have helped!). I don't think I can add that now, because I have the 'cleaned up' list as well, and I would have to go through making the identifier numbers match up properly between the two lists.

There is no single column in my list that uniquely identifies a record. Some people have the same names, or work at the same company, or have the same title, or even all three (but have a different phone number/address, etc). The tricky thing is I need a way to compare entire rows to each other, not just a single cell in each row.

Any ideas?

**martindwilson** · 04-19-2011, 12:01 PM

to compare rows just add them together in another column
eg =a2&b2&c2&d2&e2
then countif against them

**smohyee** · 04-19-2011, 12:04 PM

Hello all,

Just found this on the Microsoft site:

http://office.microsoft.com/en-us/ex...001103915.aspx

Basically answered my question. Of course, it wants a unique identifier for each record, but it also gave me a clue on how to do that: I'm thinking that if I need the entire row of data to uniquely identify, then I just create a column with the concatenation of all the cells in a row, then use the MATCH function on the sucker.

**smohyee** · 04-19-2011, 12:15 PM

martindwilson: thanks for the post! I did concatenate all the cells in a row and used it as an identifier (figured that out right before you posted), but I used your advice with the COUNTIF function, which I think was simpler to use than MATCH. Now I have a column of unique identifiers, and a second column saying either "1" or "2" for each record. And now I just filter for all that have "1" in that column, and I have my deleted rows.

Awesome. Thanks to both of you for the suggestions!