use reference cell to return dropdown list of partial matches

[solidENM]

Hello,
I am trying to pullup a customer# based on their name in cell D1. There can be many customers with similar names. I am looking for help to populate a drop down list which the user can select to get the exact match. The cell D1 is populated by a designer, who may use apostrophes, nicknames, etc.

workbook 1: cell D1 has customer name
workbook 2: Col B has customer name, Col D has customer#

I was able to use vlookup in wb1 (cell F1) to find a partial match in wb2. I think i need to scrap this and somehow turn it into a dropdown list of partial matches. A partially matched # is useless since there is no way of knowing if its correct or not.

cell f1: dropdown box of partial matches from another list based on the text in cell d1.

I will use this customer# to pullup customer specific info stored on other workbooks that do not have customer names on them.

 

[solidENM]

Still working on this. I have been looking into making the two columns a filter, and using a reference cell to auto filter columns A and B. My list is over 10K long, so I need to be able to narrow down to matches based on each work in the search cell.



[TABLE="width: 652"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Column B[/TD]
[TD] [/TD]
[TD]Column D[/TD]
[TD] [/TD]
[TD]Column F[/TD]
[/TR]
[TR]
[TD]customer_name[/TD]
[TD] [/TD]
[TD]customer_id[/TD]
[TD] [/TD]
[TD]Johns Pizza[/TD]
[/TR]
[TR]
[TD]PZ Domino[/TD]
[TD] [/TD]
[TD]10109[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Johns Deli[/TD]
[TD] [/TD]
[TD]10112[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Domino's Pizza[/TD]
[TD] [/TD]
[TD]10113[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Johns Auto[/TD]
[TD] [/TD]
[TD]10114[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]mikes market[/TD]
[TD] [/TD]
[TD]10115[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]market street[/TD]
[TD] [/TD]
[TD]10116[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 566"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]


with the above cells, id like columns B and D reduced to include any partial matched customer.
[TABLE="width: 652"]
<tbody>[TR]
[TD]Johns Deli[/TD]
[TD] [/TD]
[TD]10112[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Domino's Pizza[/TD]
[TD] [/TD]
[TD]10113[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Johns Auto[/TD]
[TD] [/TD]
[TD]10114
[/TD]
[/TR]
</tbody>[/TABLE]


any help would be appreciated. Ideally, id like a message box that asks the user to select which Customer_id to use, then continue the macro after pasting that value into a cell. Right now, im trying to make a few small steps to actually produce some progress here.

 

[DanteAmor]

Try this

Check the following file to test.

https://www.dropbox.com/s/sdgvdy2edyhqqbk/varios 09may2019c advanced filter.xlsm?dl=0


On sheet1 put your information.
On sheet2, cell A2 writes your words and executes the macro

Sub Macro2()
    Dim sh1 As Worksheet, sh2 As Worksheet, lr1 As Long, lr2 As Long, i As Long
    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")
    sh2.Range("H:H").ClearContents
    sh2.Range("H1").Value = sh2.Range("A1").Value
    datas = Split(sh2.Range("A2").Value, " ")
    For i = 0 To UBound(datas)
        Range("H" & Rows.Count).End(xlUp)(2).Value = "*" & datas(i) & "*"
    Next
    lr1 = sh1.Range("B" & Rows.Count).End(xlUp).Row
    lr2 = sh2.Range("H" & Rows.Count).End(xlUp).Row
    sh1.Range("B1:D" & lr1).AdvancedFilter xlFilterCopy, sh2.Range("H1:H" & lr2), sh2.Range("A4:C4")
End Sub

 

[solidENM]

Thank you very much Dante! That worked better than I would have expected.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[solidENM]

Hey Dante,
I was able to implement your code, thank you very much. Is there anything i can modify so the filter pulls up items that only contain both/all keywords? Right now its pulling up everything matching any word. (I know thats what i asked for, but i want to run a 2nd level filter that reduces them if the 1st finds more than x# the first time.

I am not familiar with "datas" and how that functions.

 

[DanteAmor]

Try this

Sub Macro2()
    Dim sh1 As Worksheet, sh2 As Worksheet, lr1 As Long, lr2 As Long, i As Long
    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")
    sh2.Range("H:Z").ClearContents
    sh2.Range("H1").Value = sh2.Range("A1").Value
    datas = Split(sh2.Range("A2").Value, " ")
    j = Columns("H").Column
    For i = 0 To UBound(datas)
        'Range("H" & Rows.Count).End(xlUp)(2).Value = "*" & datas(i) & "*"
        sh2.Cells(1, j).Value = sh2.Range("A1").Value
        sh2.Cells(2, j).Value = "*" & datas(i) & "*"
        j = j + 1
    Next
    lr1 = sh1.Range("B" & Rows.Count).End(xlUp).Row
    lr2 = sh2.Range("H" & Rows.Count).End(xlUp).Row
    sh1.Range("B1:D" & lr1).AdvancedFilter xlFilterCopy, sh2.Range(sh2.Cells(1, "H"), sh2.Cells(2, j)), sh2.Range("A4:C4")
End Sub

 

[solidENM]

Thanks again Dante! did you create this with the macro recorder, or all from code? When I began working with macros, I was unaware of the macro recorder. I learned a lot about coding, but the macro recorder tends to create more complex code than i could write.

 

[DanteAmor]

Thanks again Dante! did you create this with the macro recorder, or all from code? When I began working with macros, I was unaware of the macro recorder. I learned a lot about coding, but the macro recorder tends to create more complex code than i could write.

I did all the code.
But I still use the macro recorder when I do not remember the syntax of some instruction or when it is something new for me.