newparadigmz
asked on
Excel VBA, VLookup on repeated rows and show repeated values
I need to do a vlookup on data that will have repeated instances of the lookup value.
for example
id1
id2
id3
is looking up
id1, data1
id2, data1
id2, data2
id3, data3
id3, data1
Where there is data1, data2, data3, I need to add a row on the source id table for each.
I cannot so a sumif, because I need to show the breakdown of all the repeated id's
Please help
for example
id1
id2
id3
is looking up
id1, data1
id2, data1
id2, data2
id3, data3
id3, data1
Where there is data1, data2, data3, I need to add a row on the source id table for each.
I cannot so a sumif, because I need to show the breakdown of all the repeated id's
Please help
ASKER
This table
ID
3
4
7
8
9
Looks up this table
ID Info
1 aaples
2 pears
3 carrots
4 ipod
4 zune
4 iphone
5 blackberry
6 mac
7 pc
7 paper
7 rock
8 scissors
9 marker
10 pens
results in this table
ID Info
3 carrots
4 ipod
4 zune
4 iphone
7 pc
7 paper
7 rock
8 scissors
9 marker
a Lookup with the repeats added in as new rows
ID
3
4
7
8
9
Looks up this table
ID Info
1 aaples
2 pears
3 carrots
4 ipod
4 zune
4 iphone
5 blackberry
6 mac
7 pc
7 paper
7 rock
8 scissors
9 marker
10 pens
results in this table
ID Info
3 carrots
4 ipod
4 zune
4 iphone
7 pc
7 paper
7 rock
8 scissors
9 marker
a Lookup with the repeats added in as new rows
Ok - where are these tables, or do you hae range names? Or do you want me to make it all up and you can adapt the code?
Dave
Dave
ASKER
2 different workbooks, but i can copy paste move or whatever into 1, just need to do this once, but the second table has 50k lines and the first 4k.
no named ranges, just flat, dumb tables
Also, i just thought of something, table1 has like 5 columns total, and table2 has 20.
Would the easier way be to remove all lines in table2 that dont have the table1 ID and then vlookup the rest of the columns from table1 into table2?
Not a suggestion to you at all, was just wondering if that was good logic in your eyes.
I obviously trust any way you see 1000000% :)
no named ranges, just flat, dumb tables
Also, i just thought of something, table1 has like 5 columns total, and table2 has 20.
Would the easier way be to remove all lines in table2 that dont have the table1 ID and then vlookup the rest of the columns from table1 into table2?
Not a suggestion to you at all, was just wondering if that was good logic in your eyes.
I obviously trust any way you see 1000000% :)
>>Also, i just thought of something, table1 has like 5 columns total, and table2 has 20.
Would the easier way be to remove all lines in table2 that dont have the table1 ID and then vlookup the rest of the columns from table1 into table2?
You haven't defined what table 1 and table 2 are, but if they are in the order you posted them, it really doesn't matter. VLOOKUP is supposed to find matches, so it would skip over those that didn't.
For this solution, VLOOKUP will only find the first match, so we need to use the FIND statement to seek the NEXT match (or some other related logic, but I'm using find)
Ok - I trust you can transform this into what you need based on your actual tables.
Here's your code:
See attached demo workbook.
Dave
populateTableFromLookup-r1.xls
Would the easier way be to remove all lines in table2 that dont have the table1 ID and then vlookup the rest of the columns from table1 into table2?
You haven't defined what table 1 and table 2 are, but if they are in the order you posted them, it really doesn't matter. VLOOKUP is supposed to find matches, so it would skip over those that didn't.
For this solution, VLOOKUP will only find the first match, so we need to use the FIND statement to seek the NEXT match (or some other related logic, but I'm using find)
Ok - I trust you can transform this into what you need based on your actual tables.
Here's your code:
Option Explicit
Sub buildTable()
Dim wkb As Workbook
Dim wks As Worksheet
Dim rngList As Range
Dim rngIn As Range
Dim rngOut As Range
Dim r As Range
Dim rFind As Range
Dim firstAddress As String
Set wkb = ThisWorkbook
Set wks = wkb.ActiveSheet
Set rngList = wks.Range("A2", wks.Range("A" & wks.Rows.Count).End(xlUp))
Set rngIn = wks.Range("C2", wks.Range("C" & wks.Rows.Count).End(xlUp))
wks.Range("F2:G" & wks.Rows.Count).ClearContents
Set rngOut = wks.Range("F2")
For Each r In rngList
Set rFind = rngIn.Find(what:=r.Value, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False)
If Not rFind Is Nothing Then
firstAddress = rFind.Address
Do
rngOut.Value = rFind.Value
rngOut.Offset(, 1).Value = rFind.Offset(, 1).Value
Set rngOut = rngOut.Offset(1, 0)
Set rFind = rngIn.Find(what:=r.Value, after:=rFind, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False)
Loop While Not rFind Is Nothing And firstAddress <> rFind.Address
End If
Next r
End Sub
See attached demo workbook.
Dave
populateTableFromLookup-r1.xls
ASKER
Can you please change it so that if it's not found in table 2, it will still show the table1 ID?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
cool, I was hoping it was that, but wasn't sure cause of the second Find statement.
Can you please tell me what that second Find statement is for?
Can you please tell me what that second Find statement is for?
The second find, in the loop, is finding the NEXT match that might exist
Option Explicit
Sub buildTable()
Dim wkb As Workbook
Dim wks As Worksheet
Dim rngList As Range
Dim rngIn As Range
Dim rngOut As Range
Dim r As Range
Dim rFind As Range
Dim firstAddress As String
Set wkb = ThisWorkbook
Set wks = wkb.ActiveSheet
Set rngList = wks.Range("A2", wks.Range("A" & wks.Rows.Count).End(xlUp))
Set rngIn = wks.Range("C2", wks.Range("C" & wks.Rows.Count).End(xlUp))
wks.Range("F2:G" & wks.Rows.Count).ClearContents
Set rngOut = wks.Range("F2")
For Each r In rngList
Set rFind = rngIn.Find(what:=r.Value, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False)
If Not rFind Is Nothing Then
firstAddress = rFind.Address
Do
rngOut.Value = rFind.Value
rngOut.Offset(, 1).Value = rFind.Offset(, 1).Value
Set rngOut = rngOut.Offset(1, 0)
Set rFind = rngIn.Find(what:=r.Value, after:=rFind, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False)
Loop While Not rFind Is Nothing And firstAddress <> rFind.Address
Else
rngOut.Value = r.Value
Set rngOut = rngOut.Offset(1, 0)
End If
Next r
End Sub
Dave