I don't want to do this anymore - help me automate it!

April 20, 2009 5:24 AM

In Excel, is there an easy (i.e. free) way to cross-reference new spreadsheet entries with another workbook to ensure there are no duplicates?

I'm totally ignorant about Excel automation, so please forgive if there's a simple way to do this. My team at work has been asked to compile a daily list of names using Excel. The only problem is that there is another, existing database of names, and there can't be any duplicates.

Is there any simple way to quickly cross-reference the two workbooks? Currently we're just opening the old database and manually searching for duplicates. The two workbooks can't be combined, for a variety of reasons.

posted by downing street memo to computers & internet (8 comments total)

You can write formulas that refer to ranges on different workbooks. As an example, if your current workbook has names in column A, and you want to check to see if any of those names are in column A of sheet1 in a workbook called "OldList" you could put this formula in Cell B1 of new worksheet, then drag it down the column:

=COUNTIF([OldList]Sheet1!$A:$A,A1)

This will return a number in column B indicating the number of times the cell in Column A (in this case A1) is listed in the OldList worksheet.

If you open both workbooks, you can go to B1, and type =COUNTIF( and then go to the other workbook and select the column of interest, then type , and select A1 in your sheet and type ) and press enter.

This only tells you if names in the new list are listed in the old list, but should give you an idea of how to proceed.
posted by i love cheese at 5:35 AM on April 20

Do you have Microsoft Access? It is part of the Microsoft Office suite. You can import each spreadsheet tab into Access and then create a query that combines unique values.
posted by a womble is an active kind of sloth at 5:36 AM on April 20

Can you make a new column in the first workbook? If so, say the names are column A in workbook 1, and in column A of workbook too. Then make column B in workbook 1 = "=IF(COUNTIF(A:A in book 2,A1),"DUPLICATE","")"

That will make column B show up as "DUPLICATE" if the name's in workbook 2, and blank if it's not.
posted by inigo2 at 5:38 AM on April 20

I love cheese - is there a way to search the entire second spreadsheet for the firstname+lastname combo? The sheet we're working from now breaks down names into first and last (columns A and B respectively), but the sheet we have to work against has names all over the place.
posted by downing street memo at 5:49 AM on April 20

If I understand what you're saying, you could search for A1 & " " & B1 -- assuming A1=firstname and B1=lastname, it'd search for "Firstname Lastname".
posted by inigo2 at 6:08 AM on April 20

You can combine any two cells with the "&" symbol. For example:

=A1&B1

If you want to put a space between first and last name use:

=A1&" "&B1

All of the formulas that people are suggesting will work, technically speaking (as will a vlookup formula, which is how I would probably handle it), but the problem you're going to have is bigger than the formula: It's very difficult to match on names. If someone is Robert in one spreadsheet and Bob in the other, then you get no match. If there is a space or a middle initial of some kind, then no match. You probably would have said this if it was available, but is there any chance that there's an ID field or something that you can use?

If not, get the names formatted in exactly the same way in the two spreadsheets (e.g., LN, FN) then match using any of the formulas that have been suggested. But be warned that you'll get a lot of false negatives and even a few false positives (different people with the same name).

Good luck.
posted by crapples at 6:10 AM on April 20

Understanding that you can't combine the workbooks can you copy and paste just the names into a seperate single worksheet and then search that for duplicates? If you use the Concatenate function you can bring the first and last names together into a single column and then sort that column for duplicates. This formula in the first cell of a column will bring first and last names together; =CONCATENATE(C2,"",B2). You can use a fill down to populate as many rows as you need for the names. Reversing the order of the cells and inserting a space plus a comma will put combine the names in last name, first name order; =CONCATENATE(B2,", "C2). Again just drag the formula down and it will fill in the names automatically for you. Then you can either sort and scan for duplicates or use Microsoft's free add in "The Duplicate Finder".
posted by X4ster at 8:36 AM on April 20

If the sheet you are working with looks like this:

       |        A      |         B     |      C       |
   1   | John          | Doe           |              |
   2   | Abe           | Vigoda        |              |

And you want to find out if OldList has a cell with "John Doe" in it, you can do the following in C1:

=COUNTIF([OldList]Sheet1!$A:$IV,A1 & " " & B1)

Where IV is the last column that could possibly have names in OldSheet.

This combines column A and B with a space between them as the name you are searching for, and looks across all the columns from A to IV for the name in OldList.

I hope this makes sense.
posted by i love cheese at 4:31 PM on April 20