[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Excel 2007 compare, remove

Posted on 2011-10-19
Medium Priority
193 Views
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
Question by:wirbicki2
• 4
• 4
• 3

LVL 33

Expert Comment

ID: 36994619
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

LVL 12

Expert Comment

ID: 36994627
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

LVL 33

Expert Comment

ID: 36994633
You can even change your formula to something like this:

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

LVL 12

Expert Comment

ID: 36994661
0

Author Comment

ID: 36994792
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

LVL 33

Accepted Solution

jppinto earned 1000 total points
ID: 36994815
just put \$ on the rows numbers, like this:

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

LVL 33

Expert Comment

ID: 36994833
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

LVL 12

Expert Comment

ID: 36994883
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 Comment

ID: 36995093
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

LVL 12

Assisted Solution

kgerb earned 1000 total points
ID: 36995256
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 Comment

ID: 36995839
Kyle - that worked perfectly - thank you!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are ā¦
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
###### Suggested Courses
Course of the Month18 days, 17 hours left to enroll