• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

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
0
mvem
Asked:
mvem
  • 4
  • 3
1 Solution
 
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
mvemAuthor Commented:
Dave,

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

mvem
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now