Solved

Excel VBA, VLookup on repeated rows and show repeated values

Posted on 2012-03-13
9
342 Views
Last Modified: 2012-03-13
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
0
Comment
Question by:newparadigmz
  • 5
  • 4
9 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 37715475
I think you need to provide a bit more clarity.  Also, can you provide a sample workbook perhaps with your request detailed there?

Dave
0
 

Author Comment

by:newparadigmz
ID: 37715625
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
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37715643
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
0
 

Author Comment

by:newparadigmz
ID: 37715730
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% :)
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 41

Expert Comment

by:dlmille
ID: 37715964
>>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:
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

Open in new window


See attached demo workbook.

Dave
populateTableFromLookup-r1.xls
0
 

Author Comment

by:newparadigmz
ID: 37716849
Can you please change it so that if it's not found in table 2, it will still show the table1 ID?
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37716923
Ok

See attached.

Dave
populateTableFromLookup-r2.xls
0
 

Author Comment

by:newparadigmz
ID: 37716965
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?
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37716973
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

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now