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.

Column B		Column D		Column F
customer_name		customer_id		Johns Pizza
PZ Domino		10109
Johns Deli		10112
Domino's Pizza		10113
Johns Auto		10114
mikes market		10115
market street		10116

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col><col><col span="2"></colgroup><tbody>
</tbody>

with the above cells, id like columns B and D reduced to include any partial matched customer.

Johns Deli		10112
Domino's Pizza		10113
Johns Auto		10114

<tbody>
</tbody>

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.