asked on

Excel 2007 compare, remove

I have two worksheets with different types of columns. Account num is the common field between the two sheets. Account num is in column A in Sheet1 and column D in Sheet2. I want to first remove any rows from Sheet2 which do not have a account numbers contained in Sheet1 (then I will sort & remove rows). I tried to do this with vlookup (then sorting) but my vlookup code does not seem to be working. Here is my formula. I get "#N/A" on every row, even though some of the account#s from Sheet2 are in Sheet1:

=VLOOKUP(D2,Sheet1!A2:A4347,2,FALSE)

jppinto

You need to change the formula to this:

=VLOOKUP(D2,Sheet1!A2:B4347,2,FALSE)

so that it can return the value from column 2

kgerb

You should check out Dave Brett's duplicate master. It will do what you want I believe and it's a handy tool to keep in your pocket.

http://xldynamic.com/source/xld.DupMaster.html

Kyle

jppinto

You can even change your formula to something like this:

=IF(ISNA(VLOOKUP(D2,Sheet1!A2:B4347,2,FALSE)),"","Match")

kgerb

I guess that link doesn't work anymore. Here's one to an article he wrote about his app.

https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_2123-Eliminating-duplicate-data-with-Duplicate-Master-V2.html?sfQueryTermInfo=1+10+30+duplic+master

wirbicki2

ASKER

jppinto: your 2nd one seems to work. This is probably a dumb question, but how do I get the formula to keep going in all of column H, while keeping the same range? This is what I have:

H2: =IF(ISNA(VLOOKUP(D2,Sheet1!A2:B4347,2,FALSE)),"","Match")
H3: =IF(ISNA(VLOOKUP(D3,Sheet1!A2:B4347,2,FALSE)),"","Match")

If i drag H2 & H3 down to copy the formula, it starts to change the range (starting with A4, then A6)...

ASKER CERTIFIED SOLUTION

jppinto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jppinto

On a formula, if you like this:

=SUM(A1:A100)

in you put the cursor on the A1 and press F4 it will fix both columns and rows, like this:

=SUM($A$1:$A$100)

If you press again, it will fix only the row, like this:

=SUM(A$1:A$100)

kgerb

wirbicki2,
You may be aware of this but using jppinto's formulas will certainly mark the values that are duplicate between the two lists. However, if you have a "lot" of data you may want Excel to delete the duplicates for you. In that case you will either need to use the duplicate master or some other VBA code. We can help you with that if you wish to pursue that route.

Kyle

wirbicki2

ASKER

jppinto:that worked thank you!

kgerb: I think I want to use the duplicate master to remove duplicates on sheet2: removing an extra row where the account# exists more than once.

Last question since you guys are so prompt with the responses. After I've sorted by "MATCH" and removed any rows from Sheet2 which are not a Match, is it possible to do the following?

I will sort Sheet1 and Sheet2 by account num. Until I run duplicate removal: for a single account num, there may be multiple instances on Sheet2. What I want to do now is have excel find account num from Sheet2ColumnD in Sheet1ColumnA and copy the data from Sheet2ColumnG to Sheet1ColumnK. How can I do this without having someone manually copy line by line? They may not match up exactly after a sort b/c there may be some things in Sheet1 which are not at all in Sheet2.

SOLUTION

kgerb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wirbicki2

ASKER

Kyle - that worked perfectly - thank you!