Delete double entries
Posted by MattH on January 07, 2002 5:24 AM
I need to delete pairs of rows that contain the same data in column A. I have 2 sets of data from seperate sources, merged together in a single sheet and sorted on Col A. The only thing in common is the account number in col A, and I have around 4000 rows. I need to be left with a set of data that is unique to my primary source, so anything in appearing in both data sources can go. Probably a simple one but I can't see it.
Posted by Tom Urtis on January 07, 2002 5:51 AM
Would this help you? Select the data just in column A, and click on Data > Filter > Advance Filter, choose "Filter the list, in place", select "Unique records only", and click OK. Select your entire range now, which is filtered to only show rows with unique data in column A. Copy and paste it anywhere else you want. Your old data is still intact, which you can delete if you are certain you'll never need it.
HTH
Tom Urtis
Posted by Matthew Halliday on January 07, 2002 6:02 AM
Tom
sorry - I Tried that. What I need is to remove those users who have email addresses (in col H) leaving me with those who don't. But only one row of the pair has the email info. The other row has unrelated info. If I have 2 rows with a/c no. E11111 then I need to delete BOTH rows and remove that account no. from the list. There are only about 400 out of 4000 rows that have email addresses. I ned to create a list of those accounts without email addresses.
Thanks anyway
Posted by Tom Urtis on January 07, 2002 6:35 AM
OK, if I understand you correctly, you want to accomplish 2 things:
One: Delete records with duplicate data in column A. If that is the case, try this:
(1) Sort the range by column A.
(2) If your data starts in A2, then in the second row of next available column (say M2, keeping M1 blank), enter =IF(OR(A2=A1,A2=A3),1,0) and copy down to row 4000 (last row).
(3) Copy > Paste special, Value column M.
(4) Select entire range (M included), Data > AutoFilter> criteria 0. This will display only unique records based on column A, which you can copy elsewhere if desired.
(5) Delete column M.
If you further want to search column H for non-email addresses, try a second filter immediately after, on column H, criteria Custom, which would be Case 2 in that dialog box, value to filter for is the character @, or something unique to your people's email addresses.
If I'm off base here, sorry, but I hope I understood your data range correctly now.
Tom Urtis
Tom sorry - I Tried that. What I need is to remove those users who have email addresses (in col H) leaving me with those who don't. But only one row of the pair has the email info. The other row has unrelated info. If I have 2 rows with a/c no. E11111 then I need to delete BOTH rows and remove that account no. from the list. There are only about 400 out of 4000 rows that have email addresses. I ned to create a list of those accounts without email addresses. Thanks anyway : Would this help you? Select the data just in column A, and click on Data > Filter > Advance Filter, choose "Filter the list, in place", select "Unique records only", and click OK. Select your entire range now, which is filtered to only show rows with unique data in column A. Copy and paste it anywhere else you want. Your old data is still intact, which you can delete if you are certain you'll never need it. : HTH : Tom Urtis :
Posted by Matthew Halliday on January 07, 2002 6:50 AM
Hey! It worked!! Thanks Tom - I'm left with 2674 records with no email! All I have to do is sort them by company - easy - and I'm done!
I knew it would be simple - I've just never done it before. OK, if I understand you correctly, you want to accomplish 2 things: One: Delete records with duplicate data in column A. If that is the case, try this: (1) Sort the range by column A.
