use reference cell to return dropdown list of partial matches

solidENM

Board Regular
Joined
Feb 23, 2017
Messages
87
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.
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

solidENM

Board Regular
Joined
Feb 23, 2017
Messages
87
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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

Code:
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

Board Regular
Joined
Feb 23, 2017
Messages
87
Thank you very much Dante! That worked better than I would have expected.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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

solidENM

Board Regular
Joined
Feb 23, 2017
Messages
87
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Try this

Code:
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
 
Last edited:

solidENM

Board Regular
Joined
Feb 23, 2017
Messages
87
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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.;)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,985
Messages
5,575,381
Members
412,658
Latest member
LS0009
Top