How would you accomplish this most easily?
- Thread starter AdHustler
- Start date
How to remove duplicate records or create list of unique records in Excel
or get out of excel, copy both lists into textpad. click sort. Check the remove duplicates. The resulting is List A
or get out of excel, copy both lists into textpad. click sort. Check the remove duplicates. The resulting is List A
i tried what was in that link.
What it seems to do is remove the duplicates. For instance if there are 12 records and 2 of the records have duplicates, it reduces the list to 10 records.
What i need to do is remove any instance of those records altogether. Maybe i just missed it or did something wrong.
Thanks for the help though.
What it seems to do is remove the duplicates. For instance if there are 12 records and 2 of the records have duplicates, it reduces the list to 10 records.
What i need to do is remove any instance of those records altogether. Maybe i just missed it or did something wrong.
Thanks for the help though.
You could use the vlookup and iserror functions within Excel that will check to see if the item in List 1 is in List 2. If it's not, it'll output the item in list 1. If the item does exist, it outputs nothing.
=IF(ISERROR(VLOOKUP(A1,B:B,1,0)),A1,"")
Excel: VLookup Function
=IF(ISERROR(VLOOKUP(A1,B:B,1,0)),A1,"")
Excel: VLookup Function
Here are two methods using VBA
Quickly Eliminate Duplicate Records From A Specific Column | Excelguru.ca
ELIMINATE DUPLICATE RECORDS IN EXCEL? - Yahoo! Answers
Quickly Eliminate Duplicate Records From A Specific Column | Excelguru.ca
ELIMINATE DUPLICATE RECORDS IN EXCEL? - Yahoo! Answers
1) Paste list B on the end of list A. Lets say this is all in column A and goes from A1 to A14500
2) In B1, put this: =COUNTIF($A1:$A14500,A1)
3) Copy B1, and paste it into all the rest of the B column beside the values in A (so all the way down to B14500)
5) B will now show you how many of the corresponding value in A there are
6) Select the whole lot, sort by column B ascending
7) now the list shows you the stuff you want first (with a '1' in the B column) and the stuff you don't want next (with a '2' in the B column), so just select and delete the stuff you don't want.
2) In B1, put this: =COUNTIF($A1:$A14500,A1)
3) Copy B1, and paste it into all the rest of the B column beside the values in A (so all the way down to B14500)
5) B will now show you how many of the corresponding value in A there are
6) Select the whole lot, sort by column B ascending
7) now the list shows you the stuff you want first (with a '1' in the B column) and the stuff you don't want next (with a '2' in the B column), so just select and delete the stuff you don't want.
HarveyJ
He is - THE CACTUS!
Importing them both into an Access database will remove duplicate entries upon importation if you have the option ticked.
I'm only recommending Access based off the lists currently being Excel files. MS products play nice with each other most of the time.
I'm only recommending Access based off the lists currently being Excel files. MS products play nice with each other most of the time.
