|
multiple answers
Help€¦ Dont 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 Bs 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 |
multiple answers
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 "Carolina Girl" wrote: Help€¦ Dont 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 Bs 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 |
multiple answers
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" wrote: 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 "Carolina Girl" wrote: Help€¦ Dont 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 Bs 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 |
multiple answers
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" wrote: 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" wrote: 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 "Carolina Girl" wrote: Help€¦ Dont 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 Bs 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 |
multiple answers
Hey Sheeloo
Awesome Man Greattttttttttttttttt Hardeep kanwar "Sheeloo" wrote: 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" wrote: 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" wrote: 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 "Carolina Girl" wrote: Help€¦ Dont 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 Bs 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 |
| All times are GMT +1. The time now is 02:44 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com