How do I remove duplicates and then merge two excel files?

February 7, 2007 6:25 PM

In MS Excel, how do I remove duplicates, clean up a document and merge two files together?

I have found a few threads in Google, but none of them have quite what I'm looking for, I'm hoping someone will have experience doing this. Right now, I have to do this manually, but I figure there has to be a more efficient way of completing this.

This is a bit complicated, but I'll try to simplify as best as I can:

I have two excel files, A and B.

File A contains the fields PART NUMBER and PRICE and is laid out as follows:

a1000 $1.25
a1000 $1.25
a1000 total $2.50
a1001 $3.25
a1001 total $3.25
a1002 $3.31
a1002 $3.31
a1002 total $6.62
etc. etc.

Questions:
1) How do I remove all the duplicate records?
2) How do I remove all the rows that contain the word "total" in the PART NUMBER field?

File B contains a master list of a lot more information for other parts and the formatting needs to be retained. After cleaning up File A, is there a import those values to a designated column for their corresponding part numbers in File B?

Any help would be MUCH appreciated.

posted by perpetualstroll to computers & internet (6 comments total)

Well for #2 you can do a sort by Column B and all the ones with "total" will be grouped together, you can then easily delete those.

For #1, you can select all the items and do a Data->Filter->Advanced Filter and Check off "Unique Records Only". Once filtered you can Copy and Paste the Filtered List into a new Worksheet

For the File B question, you can then use the "VLOOKUP" formula to insert a value from one Table to the other.

I'm no Excel Expert, and I'm sure there may be easier ways to do this...
posted by stew560 at 6:46 PM on February 7, 2007

1) i) Select the data
ii) alt-D-F-A-R (Data->Filter->Advanced Filter->Unique Records)
then copy to somwhere else.
2) Select column, alt-D-F-F (autofilter), then select 'custom' and 'does not contain' 'total'.
posted by pompomtom at 6:48 PM on February 7, 2007

pompomtom's solution should work for file A. For file B, it sounds like you need a VLOOKUP formula. It's tricky to explain without making some assumptions, so let's assume the following:

-- Both file A and file B are open.

-- File A is called "Book1", the data is on the tab Sheet1, the part num is in column a, the price is in column b, and the data extends from row 1 to row 1000.

-- Finally, in file B, assume that you want the price to appear in column M, that the relevant data range also begins on row 1, and that the part number in file B appears in column A.

Now, place the following formula in cell M1, and copy it down to the end of the relevant data range.

=VLOOKUP(A1,[Book1]Sheet1!$A$1:$B$1000,2,FALSE)

That translates as -- take the value in cell A1 of this sheet and look for it in the leftmost column of the range A1:B1000 on Sheet1 of Book1; after finding it, return the value from column #2 of this same range; and match exact values only.
posted by treepour at 10:58 PM on February 7, 2007

I always wondered about this in the past.

Its hard to explain. You want to use VLOOKUP as treepour correctly says.

The function will 'lookup' the cell you specify in a range you specify. Any problems write back on here.
posted by tomw at 11:19 PM on February 7, 2007

Here's a great Add-In for Excel that I think does the deduping part of what you want with minimal hassle.

The Duplicate Master

Remember to make a backup of your Workbook before running any of these functions on it!
posted by zackola at 7:36 AM on February 8, 2007

These techniques and those on the pages linked to it have saved me on all manner of Excel sorting/removing duplicates tasks. The explanations are a little terse, but the code works well. And there are several different techniques here, a combination of which should do most of what you need (if not all of it).
posted by wheat at 8:49 AM on February 8, 2007