• Status: Solved
• Priority: Medium
• Security: Public
• Views: 195

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)
0
wirbicki2
• 4
• 4
• 3
2 Solutions

Commented:
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
0

Chief EngineerCommented:
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
0

Commented:
You can even change your formula to something like this:

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

Chief EngineerCommented:
0

Author Commented:
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)...
0

Commented:
just put \$ on the rows numbers, like this:

H2: =IF(ISNA(VLOOKUP(D2,Sheet1!A\$2:B\$4347,2,FALSE)),"","Match")
0

Commented:
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)
0

Chief EngineerCommented:
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
0

Author Commented:
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.
0

Chief EngineerCommented:
If I'm understanding you correctly (and I may not be:), I think the formula will do what you want.  Put it in column K on sheet1 and drag down.

=INDEX(Sheet2!\$G\$1:\$G\$100,MATCH(A1,Sheet2!\$D\$1:\$D\$100,0))

Kyle
0

Author Commented:
Kyle - that worked perfectly - thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.