How to merge worksheets on a key - merge horizontally, that is

  • Thread starter gimme_this_gimme_that
  • Start date
G

gimme_this_gimme_that

Suppose Sheet1 looks like this:

KEY|VALUE
100 | Chevy
200 | Honda

Suppose Sheet2 looks like this:

KEY|VALUE
100 | Corvair
100 | Corvette
100 | Impala
200 | Accord
200 | Civic

How do I create Sheet3?

100|Chevy|Corvair
100|Chevy|Corvette
100|Chevy|Impala
200|Honda|Accord
200|Honda|Civic

Thanks.
 
D

Dave Peterson

You can actually just insert a new column B in Sheet2--or copy sheet2 to sheet3
and do the work there.

In B1, put this formula:
=vlookup(a1,sheet1!a:b,2,false)

and drag down the column.
 
G

gimme_this_gimme_that

In Sheet 2, Column C I add the formula:

=VLOOKUP(A1,sheet1:A:B,1,FALSE)

This results in #NAME?

Is there a solution that doesn't require drag-filling?
 
I

Incidental

Hey

or you could go the long way round...

Option Explicit
Dim MyCell1, MyRng1 As Range
Dim MyCell2, MyRng2 As Range
Dim MyCell3, MyRng3 As Range
Dim MyStart, MyEnd1, MyEnd2, MyEnd3 As String

Private Sub CommandButton1_Click()
MyStart = "A1"
Sheets(2).Select
[A1].End(xlDown).Select
MyEnd1 = ActiveCell.Address
Range(MyStart, MyEnd1).Copy Sheets(3).[A1]
Set MyRng1 = Range(MyStart, MyEnd1)


Sheets(3).Select
[A1].End(xlDown).Select
MyEnd3 = ActiveCell.Address
Set MyRng3 = Range(MyStart, MyEnd3)
Sheets(1).Select
[A1].End(xlDown).Select
MyEnd2 = ActiveCell.Address
Set MyRng2 = Range(MyStart, MyEnd2)

For Each MyCell2 In MyRng2
For Each MyCell3 In MyRng3
If MyCell3 = MyCell2 Then
MyCell3.Offset(0, 1).Value = MyCell2.Offset(0, 1).Value
End If
Next MyCell3
Next MyCell2
Sheets(3).Select
[A1].Select
For Each MyCell1 In MyRng1
If ActiveCell = MyCell1 Then
ActiveCell.Offset(0, 2).Value = MyCell1.Offset(0, 1).Value
ActiveCell.Offset(1, 0).Select
End If
Next MyCell1
End Sub

S
 
D

Dave Peterson

Watch your typing <bg>.

=VLOOKUP(A1,sheet1:A:B,1,FALSE)
should be
=VLOOKUP(A1,sheet1!A:B,2,FALSE)
or
=VLOOKUP(A1,'sheet 1'!A:B,2,FALSE)
(single quotes maybe required depending on the name of that sheet)

And you want that 3rd parm to be 2--you're bringing back the value in the second
column in that range (A:B on sheet1).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top