How do I search between two Excel sheets?

Hello,

Please see the attached Excel sheet.  I am trying to do the following:

1) For each value in 'Sheet1' column 'B', search 'Sheet2' column 'A' for an identical value.

2)  If an identical value exists in 'Sheet2', place the value from 'Sheet2' column 'B' into 'Sheet1' column 'A'

Is there way to do this using VB?

Thanks,

mvem
Example.xlsx
mvemAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DaveCommented:
The quickest way is to define a working range with VBA then fill it with a "normal" formula automatically.

Like below

It avoids long VBA loops


Note that the sheet2 name is hardcoded in this example

Dave

Sub QuikckFIll()
Dim ws1 As Worksheet
Dim rng1 As Range
Set ws1 = Sheets(1)
Set rng1 = ws1.Range(ws1.[b2], ws1.Cells(Rows.Count, "B").End(xlUp))
rng1.Offset(0, -1).FormulaR1C1 = _
        "=IF(ISNA(MATCH(RC[1],Sheet2!C,0)),"""",INDEX(Sheet2!C[1],MATCH(RC[1],Sheet2!C,0)))"
End Sub

Open in new window

0
mvemAuthor Commented:
Hey Dave,

Thanks for the help.  I had two questions for you:

1)  Line 5 looks like it sets up the range of the search, correct?

2)  Could you explain what is going on in line 7?

Thanks,

mvem
0
DaveCommented:
> Set rng1 = ws1.Range(ws1.[b2], ws1.Cells(Rows.Count, "B").End(xlUp))

correct
it says make a continous range, starting from B2 then to the cell which is retrieved after look from the bottom of column B up till the last used cell. so say B2:B10

> 2)  Could you explain what is going on in line 7?
rng1.Offset(0, -1).FormulaR1C1
says move one column to the left of the range we set in Step 1. ie work on A2:A10

A formula is inserted to these cells.

I simply used the macro recorder to copy the code that results from entering this formula into B2
=IF(ISNA(MATCH(B2,Sheet2!A:A,0)),"",INDEX(Sheet2!B:B,MATCH(B2,Sheet2!A:A,0)))

with relative references to column A this is the VBA
  "=IF(ISNA(MATCH(RC[1],Sheet2!C,0)),"""",INDEX(Sheet2!C[1],MATCH(RC[1],Sheet2!C,0)))"

Regards

Dave


0
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

mvemAuthor Commented:
Hey Dave,

Could you explain what all is going on here:

"=IF(ISNA(MATCH(RC[1],Sheet2!C,0)),"""",INDEX(Sheet2!C[1],MATCH(RC[1],Sheet2!C,0)))"

Sorry for all the questions..I am new to Excel and VB and like to know what is happening so I can apply it down the road rather than just blindly copying and pasting.

mvem
0
DaveCommented:
Hi mevm,

Beyond what I've stated ie,

A formula is inserted to these cells.

I simply  used the macro recorder to copy the code that results from entering this  formula into B2
=IF(ISNA(MATCH(B2,Sheet2!A:A,0)),"",INDEX(Sheet2!B:B,MATCH(B2,Sheet2!A:A,0)))

with  relative references to column A this is the VBA
   "=IF(ISNA(MATCH(RC[1],Sheet2!C,0)),"""",INDEX(Sheet2!C[1],MATCH(RC[1],Sheet2!C,0)))"

I don't there is much I can add further - its just the VBA to insert the formula

this part
MATCH(B2,Sheet2!A:A,0)
returns the position in the list in column A of sheet 2 for B2 in sheet 1

this part
INDEX(Sheet2!B:B,MATCH(B2,Sheet2!A:A,0))
says return the value from Sheet 2 column B from the same position that the Match was made

Cheers

Dave
0
DaveCommented:
Hi mevm,

Beyond what I've stated ie,

A formula is inserted to these cells.

I simply  used the macro recorder to copy the code that results from entering this  formula into B2
=IF(ISNA(MATCH(B2,Sheet2!A:A,0)),"",INDEX(Sheet2!B:B,MATCH(B2,Sheet2!A:A,0)))

with  relative references to column A this is the VBA
   "=IF(ISNA(MATCH(RC[1],Sheet2!C,0)),"""",INDEX(Sheet2!C[1],MATCH(RC[1],Sheet2!C,0)))"

I don't there is much I can add further - its just the VBA to insert the formula

this part
MATCH(B2,Sheet2!A:A,0)
returns the position in the list in column A of sheet 2 for B2 in sheet 1

this part
INDEX(Sheet2!B:B,MATCH(B2,Sheet2!A:A,0))
says return the value from Sheet 2 column B from the same position that the Match was made

Cheers

Dave
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mvemAuthor Commented:
Dave,

Thanks for clarifying even more...appreciate your time.

mvem
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.