multiple answers

[Carolina Girl]

Help… Don’t know if I can explain this correctly….. I am looking for a
formula that will do a vlookup on another sheet and tell me multiple answers.
If there are two columns in sheet 1 and 123 shows up twice what I need on
sheet 2 is columns B’s information for 123 to show up with each time it is
listed
Sheet 1
A B
123 xyz
132 hig
123 jkl

Sheet 2
A B C
123 xyz jkl

 

[Sheeloo]

Try the macro
Sub populate()
With Sheets("Sheet2")
lastRow1 = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
With Sheets("Sheet1")
lastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

For i = 2 To lastRow1

k = 2
For j = 2 To lastRow2
If Sheets("Sheet2").Cells(i, 1).Value = Sheets("Sheet1").Cells(j, 1).Value
Then
Sheets("Sheet2").Cells(i, k).Value = Sheets("Sheet1").Cells(j, 2).Value
k = k + 1
End If
Next j
Next i
End Sub

 

[Hardeep kanwar]

Great Sheeloo

I have a same problem but in a different Way
Say Sheet 1 has data like this
Code Name Total
123 ABC 500
123 DEF 200
123 GHI 300
456 JKL 400
456 MNO 325
789 PQR 265
789 STU 125
457 XYZ 100

Now I want in Sheet 2 to Look like this

123 ABC 500 DEF 200 GHI 300
456 JKL 400 MNO 325
789 PQR 265 STU 125
457 XYZ 100


Thanks in Advance

 

[Sheeloo]

Hardeep,

Try
Sub populate()

With Sheets("Sheet1")
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

i = 1
k = 2
j = 1

ID = Sheets("Sheet1").Cells(i, 1).Value
Sheets("Sheet2").Cells(j, 1).Value = ID
Sheets("Sheet2").Cells(j, k).Value = Sheets("Sheet1").Cells(i, 2).Value
Sheets("Sheet2").Cells(j, k + 1).Value = Sheets("Sheet1").Cells(i, 3).Value
k = k + 2

For i = 2 To lastRow

If Sheets("Sheet1").Cells(i, 1).Value = ID Then
Sheets("Sheet2").Cells(j, k).Value = Sheets("Sheet1").Cells(i, 2).Value
Sheets("Sheet2").Cells(j, k + 1).Value = Sheets("Sheet1").Cells(i, 3).Value
k = k + 2
Else
k = 2
j = j + 1
ID = Sheets("Sheet1").Cells(i, 1).Value
Sheets("Sheet2").Cells(j, 1).Value = ID
Sheets("Sheet2").Cells(j, k).Value = Sheets("Sheet1").Cells(i, 2).Value
Sheets("Sheet2").Cells(j, k + 1).Value = Sheets("Sheet1").Cells(i, 3).Value
k = k + 2

End If

Next i
End Sub

 

[Hardeep kanwar]

Hey Sheeloo

Awesome Man Greattttttttttttttttt

Hardeep kanwar