Comparing names in excel
September 7, 2006 12:59 PM Subscribe
How do I compare lists of names from two excel spreadsheets?
Say I have two excel spreadsheets that have a list of names and other info. I want to be able to have excel check the first list to see if any of the names are duplicates on the second list, and if they are delete them from the first list. Is there any easy way to do this, even if I have to do the deleting myself?
Say I have two excel spreadsheets that have a list of names and other info. I want to be able to have excel check the first list to see if any of the names are duplicates on the second list, and if they are delete them from the first list. Is there any easy way to do this, even if I have to do the deleting myself?
There a number of ways to do this--probably easiest to make a column in one sheet and run the "countif" command. Your inputs are the list of names you're checking against, and the name you're checking. It should look like this:
=countif("range of names to check", "name to check against")
Fill this formula down, so it checks each name, then you can sort by this column (all the names that are original should say 0, and the rest should say 1 or more, if they're duplicates.)
posted by crookedneighbor at 1:06 PM on September 7, 2006
=countif("range of names to check", "name to check against")
Fill this formula down, so it checks each name, then you can sort by this column (all the names that are original should say 0, and the rest should say 1 or more, if they're duplicates.)
posted by crookedneighbor at 1:06 PM on September 7, 2006
I have used Access to accomplish this in the past.
I did find this tutorial though
posted by thenextword at 1:09 PM on September 7, 2006
Assuming that you have two sheets SHEET1 and SHEET2. Assuming that your data are in column A in both sheets. Assuming that you want to delete items from SHEET1 that are not in SHEET2.
Put the following formula in cell B1 in SHEET1:
=IF(COUNTIF(Sheet2!A:A,Sheet1!A1)>0,A1,"")
Copy-and-paste it all the way down column B of SHEET1. You now have a list of all the cells from column A that are matched in SHEET2, albeit with probably quite a few blank cells (but you can get rid of those by just sorting column B in alphabetical order).
You could also use one of my favorites, VLOOKUP:
=IF(ISNA(VLOOKUP(A1,Sheet2!A:A,1,FALSE)),"",VLOOKUP(A1,Sheet2!A:A,1,FALSE))
posted by Doofus Magoo at 1:09 PM on September 7, 2006
Put the following formula in cell B1 in SHEET1:
=IF(COUNTIF(Sheet2!A:A,Sheet1!A1)>0,A1,"")
Copy-and-paste it all the way down column B of SHEET1. You now have a list of all the cells from column A that are matched in SHEET2, albeit with probably quite a few blank cells (but you can get rid of those by just sorting column B in alphabetical order).
You could also use one of my favorites, VLOOKUP:
=IF(ISNA(VLOOKUP(A1,Sheet2!A:A,1,FALSE)),"",VLOOKUP(A1,Sheet2!A:A,1,FALSE))
posted by Doofus Magoo at 1:09 PM on September 7, 2006
Save the two spreadsheets as Word docs, then compare them using the compare function (try the Tools menu).
(There are other programs that can compare the two files, but Word is one program probably everyone has if they have Excel.)
posted by pracowity at 1:18 PM on September 7, 2006
(There are other programs that can compare the two files, but Word is one program probably everyone has if they have Excel.)
posted by pracowity at 1:18 PM on September 7, 2006
i second VLOOKUP as the most efficient way of doing this (should take about 1 min to find out if there are duplicates)
posted by barrakuda at 2:37 PM on September 7, 2006
posted by barrakuda at 2:37 PM on September 7, 2006
Best answer: copy / paste the second list into the first (new sheet is best (sheet2)
on the original sheet, on the column next to the one with the data, use the following:
=if(isnumber(match(Sheet1!A1,Sheet2!A:A,0)),1,0)
I may have the order wrong in the match command, excel tells you what it needs though when you open the bracket. Source, where to look for it, and match type. 0 is what you want for a perfect match. This will put ones in the column if it's true, and and zeros if not. If you go to data - filter - autofilter, and then filter by zeros, you can select the rows with zeros (no match in the new list) and delete 'em.
www.mrexcel.com is also an excellent resourse for all excel questions. (no affiliation, just a user!)
posted by defcom1 at 3:04 PM on September 7, 2006 [1 favorite]
on the original sheet, on the column next to the one with the data, use the following:
=if(isnumber(match(Sheet1!A1,Sheet2!A:A,0)),1,0)
I may have the order wrong in the match command, excel tells you what it needs though when you open the bracket. Source, where to look for it, and match type. 0 is what you want for a perfect match. This will put ones in the column if it's true, and and zeros if not. If you go to data - filter - autofilter, and then filter by zeros, you can select the rows with zeros (no match in the new list) and delete 'em.
www.mrexcel.com is also an excellent resourse for all excel questions. (no affiliation, just a user!)
posted by defcom1 at 3:04 PM on September 7, 2006 [1 favorite]
=IF(MATCH(Sheet1!A1,Sheet2!A:A,0),"Duplicate in row "&TEXT(MATCH(Sheet1!A1,Sheet2!A:A,0),"0"),"")
And you want to do a Data|Sort on Sheet2 Column A first.
Go to sheet2, delete the bad boy from the row there, and go back to sheet1. Keep going till the duplicates are gone.
Job done!
posted by nj_subgenius at 3:18 PM on September 7, 2006
And you want to do a Data|Sort on Sheet2 Column A first.
Go to sheet2, delete the bad boy from the row there, and go back to sheet1. Keep going till the duplicates are gone.
Job done!
posted by nj_subgenius at 3:18 PM on September 7, 2006
...you want to copy this for all rows in Sheet1, obviously...
posted by nj_subgenius at 3:21 PM on September 7, 2006
posted by nj_subgenius at 3:21 PM on September 7, 2006
This thread is closed to new comments.
posted by mattbucher at 1:04 PM on September 7, 2006