[thelist] OT -deleting dupLicates in excel document
Roger Ly
evolt at matchpenalty.com
Thu May 5 11:34:00 CDT 2005
>Is there a relatively quick way to get rid of the duplicates
>in excel without doing in manually? We're talking about 6,000 names
>here.
Bruce,
Learned this from a co-worker who claims there isn't an automated way to
delete/find duplicates:
Add an extra column next to your email column, let's assume your email is in
column A, and that new column is column B. (Data goes from A1 -> A6000).
Sort the data by column A, so all your email addresses are sorted.
In column B1, enter in the value FALSE.
In column B2, enter the formula (without quotes)
"=A1=A2"
Copy that formula in data cells B3->B6000
Now that B column has values telling you if the current row has the same
value as the previous row.
Now, select all the data in column B, and re-paste it into that column using
Paste Special -> Paste: Values. (Make sure you choose the actual values and
not the formula). This will allow you to sort on column B in the next step.
Now, take all your data, and sort it by column B.
The top set of values in column B will be "FALSE", meaning that these were
the original values.
The bottom set will be "TRUE" meaning that these were duplicate values.
Just delete all rows whose value in column B is TRUE.
HTH,
Roger
More information about the thelist
mailing list