VBA vlookup multiple results

dappy

Board Regular
Joined
Apr 23, 2018
Messages
102
Office Version
  1. 2013
Platform
  1. Windows
Hi folks,

Hopefully someone out there can help.

I have a list to vlookup which works (ish). the lookup only picks up the first instance of the source and not all instances.

sourcerequestedoutput
table-500227table--500227/line-110/output_2
500227​
table-500227table--500227/line-110/output_2
table-500227table--500227/line-120/output_2
500163​
table-500163table--500163/line-110/output_2
table-500227table--500227/line-130/output_2
500165​
table-500165table--500165/line-110/output_2
table-500163table--500163/line-110/output_2
table-500163table--500163/line-120/output_2
table-500163table--500163/line-130/output_2
table-500165table--500165/line-110/output_2
table-500165table--500165/line-120/output_2

the code i have that doesnt work completely

Dim Rw As Long
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim WsF As Object
Set WsF = Application.WorksheetFunction


Set ws1 = ThisWorkbook.Sheets("dump")
Set ws2 = ThisWorkbook.Sheets("batch_list")
Set ws3 = ThisWorkbook.Sheets("output")

For Rw = 1 To 1000
On Error Resume Next
ws3.Cells(Rw, "A") = WsF.VLookup(ws2.Cells(Rw, "A"), ws1.Range("A1:b30000"), 2, False)
Next Rw

For Rw = 1 To 27
On Error Resume Next
ws3.Cells(Rw, "c") = WsF.VLookup(ws2.Cells(Rw, "A"), ws1.Range("d1:e30000"), 2, False)
Next Rw

any help or guidance much appreciated as normal
thanks in advance
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

CsJHUN

Active Member
Joined
Jan 13, 2015
Messages
348
Office Version
  1. 2016
Platform
  1. Windows
Hi Dappy,
i would use string formula and pivot
Sheet1
ABCD
1sourcetblnoSorcímkék
2table-500227table--500227/line-110/output_2500227500163
3table-500227table--500227/line-120/output_2500227table-500163table--500163/line-110/output_2
4table-500227table--500227/line-130/output_2500227table-500163table--500163/line-120/output_2
5table-500163table--500163/line-110/output_2500163table-500163table--500163/line-130/output_2
6table-500163table--500163/line-120/output_2500163500165
7table-500163table--500163/line-130/output_2500163table-500165table--500165/line-110/output_2
8table-500165table--500165/line-110/output_2500165table-500165table--500165/line-120/output_2
9table-500165table--500165/line-120/output_2500165500227
10table-500227table--500227/line-110/output_2
11table-500227table--500227/line-120/output_2
12table-500227table--500227/line-130/output_2
13Végösszeg
Munka1
Cell Formulas
RangeFormula
B2:B9B2= MID(A2,7,6)

1603804106171.png

Sorry because its hungarian, but i think and hope you get the point and method
 

dappy

Board Regular
Joined
Apr 23, 2018
Messages
102
Office Version
  1. 2013
Platform
  1. Windows
thanks CsJHUN, but unfortunately i need the code in VBA, its part of a big project and wont have any ability to do a pivot table.
thanks for looking though
 

CsJHUN

Active Member
Joined
Jan 13, 2015
Messages
348
Office Version
  1. 2016
Platform
  1. Windows
Well,
i generated this:
Munkafüzet1
ABC
1sourcerequestedOutput
2table-500227table--500227/line-110/output_2500227
3table-500227table--500227/line-120/output_2500227table-500227table--500227/line-110/output_2
4table-500227table--500227/line-130/output_2500227table-500227table--500227/line-120/output_2
5table-500163table--500163/line-110/output_2500227table-500227table--500227/line-130/output_2
6table-500163table--500163/line-120/output_2500165
7table-500163table--500163/line-130/output_2500165table-500165table--500165/line-110/output_2
8table-500165table--500165/line-110/output_2500165table-500165table--500165/line-120/output_2
9table-500165table--500165/line-120/output_2
Munka1


with this code
VBA Code:
Sub Dappy_mrexcel()
'https://www.mrexcel.com/board/threads/vba-vlookup-multiple-results.1149524/
Dim request_cell As Variant
Dim search_cell As Variant
request_rows = Range("B" & Rows.Count).End(3).Row
search_rows = Range("A" & Rows.Count).End(3).Row
b = 1
Do
b = b + 1
    req_id = Range("B" & b).Value
    For A = 2 To search_rows
        search_id = Range("A" & A).Value
        On Error Resume Next
        str_find = InStr(1, search_id, req_id) > 0
        If str_find Then
        b_plus = b_plus + 1
            Range("B" & b + b_plus & ":D" & b + b_plus).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            Range("B" & b + b_plus).Value = req_id
            Range("C" & b + b_plus).Value = search_id
        End If
    Next A
    b = b + b_plus
    request_rows = Range("B" & Rows.Count).End(3).Row
    b_plus = 0
  
Loop Until b >= request_rows
End Sub
It could be shortened but i usually do this to make it more read and debugable for my users.7

Oh my bad, column B is manually added on my side.
My code could fit/replace your "For RW 1 to 27 ...." loop
 

dappy

Board Regular
Joined
Apr 23, 2018
Messages
102
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

thanks again, i tried to simplify what i was after but seems i've caused more problems.

the data is split between 3 sheets.

source is in sheet "dump"
request is in sheet "batch_list"
output is in sheet "output"

so can i modify your code to include these changes? apologies, i dont really follow how you get the 6 figure number out of the string!
 

CsJHUN

Active Member
Joined
Jan 13, 2015
Messages
348
Office Version
  1. 2016
Platform
  1. Windows
Sorry for the delay, i wasnt online, here is a working vba for:
- 3 sheets, names according to your original macro ('dump' is not used)
- get all request numbers from ws2 (batch list) to ws3 (output)
- make these request numbers uniqe (by removing duplicates)
- loop trhu this request numbers check them on ws2 on each cell, if they can be found on cell value

Also commented on most of the lines, could be distracting, but i hope its help you understand what is doing what.
VBA Code:
Sub Dappy_mrexcel()
'https://www.mrexcel.com/board/threads/vba-vlookup-multiple-results.1149524/
Dim request_cell As Variant
Dim search_cell As Variant
Dim Rw As Long
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim WsF As Object
Set WsF = Application.WorksheetFunction
  
Set ws1 = ThisWorkbook.Sheets("dump") 'I guess its not needed for this macro, but added anyway
Set ws2 = ThisWorkbook.Sheets("batch_list")
Set ws3 = ThisWorkbook.Sheets("output")


search_rows = ws2.Range("A" & Rows.Count).End(3).Row 'Count how many rows on 'batch_list', and not using any fix number

ws3.Range("b1").Value = "unique short ID's" 'adding some headline to output
ws3.Range("C1").Value = "ID's" 'adding some headline to output
request_count = 1
With ws2
    For Each cell In .Range("A1:A" & search_rows) 'loop thru all the cells in 'batch list', and generate the 'request list' on ws3
    request_count = request_count + 1
    ws3.Range("B" & request_count).Value = Mid(cell.Value, 7, 6) 'the first 6 digit number on the ws2 'A' column writen into the next empty cell on ws3
    Next cell
    request_rows = ws3.Range("B" & Rows.Count).End(3).Row 'counting how many lines on ws3 column 'B' for the next step (it must be equal to search_row, for now)
    ws3.Range("B1:B" & request_rows).RemoveDuplicates Columns:=1, Header:=xlNo 'removing duplicates on ws3 column 'B' get you the list of request
End With

request_rows = ws3.Range("B" & Rows.Count).End(3).Row 'counting the unique request numbers
'this part do the collecting of the request id's. In your macro its the 'For rw 1 to 27' loop
b = 1
Do
b = b + 1
    req_id = ws3.Range("B" & b).Value 'get the unique request id
    For A = 1 To search_rows
        search_id = ws2.Range("A" & A).Value 'get the batch_list item name
        On Error Resume Next
        str_find = InStr(1, search_id, req_id) > 0 'check if unique request id can be found in the batch_list name
        If str_find Then 'if can be found
            b_plus = b_plus + 1
            ws3.Range("B" & b + b_plus & ":D" & b + b_plus).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove 'insert one row below the unique request id
            ws3.Range("B" & b + b_plus).Value = req_id 'write the unique request id into cell
            ws3.Range("C" & b + b_plus).Value = search_id 'write the batch_list name into cell
        End If
    Next A 'doing it all over on ws2
    'after one unique request id is done moving to the next one
    b = b + b_plus
    request_rows = ws3.Range("B" & Rows.Count).End(3).Row
    b_plus = 0
  
Loop Until b >= request_rows
End Sub


Feel free to ask, if thats what you looking for plz mark your thread as sloved.
 

dappy

Board Regular
Joined
Apr 23, 2018
Messages
102
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Morning,

that looks fantastic but..

ws 1 is where the data is and is what is needed to be moved to ws3. ws2 is the list that needs to be used to filter what is in sw1 into ws3. is this possible?
 

dappy

Board Regular
Joined
Apr 23, 2018
Messages
102
Office Version
  1. 2013
Platform
  1. Windows
of course i can have a lookup on ws1 so column A would be the 6 digit number of what is in column B. would that make it easier?
 

CsJHUN

Active Member
Joined
Jan 13, 2015
Messages
348
Office Version
  1. 2016
Platform
  1. Windows
Morning,

that looks fantastic but..

ws 1 is where the data is and is what is needed to be moved to ws3. ws2 is the list that needs to be used to filter what is in sw1 into ws3. is this possible?
Oh I see now i totaly misread your code
of course i can have a lookup on ws1 so column A would be the 6 digit number of what is in column B. would that make it easier?
What kind of data on ws2 column 'A'?
Is it possible to add 3 screenshot or xlbb of the first few row of each sheet without any modification we made?

What data you need on 'output sheet' only "requested" and "output"?
 

dappy

Board Regular
Joined
Apr 23, 2018
Messages
102
Office Version
  1. 2013
Platform
  1. Windows
1603876931169.png
1603876956567.png
1603876976201.png


sorry dont know how to do xlbb over 3 sheets.
So the batchlist is the lookup values i need from the dump to be put in output. so ws2 has the lookup values i need from ws1 to be put into ws3. or whatever name you want :D
hope this clears things up and thank you so much for looking into this
 

Watch MrExcel Video

Forum statistics

Threads
1,113,833
Messages
5,544,574
Members
410,621
Latest member
S Oberlander
Top