Loop Vlookup

[leeandoona]

Hi all. I'm stuck trying to get either VBA or excel to perform a Vlookup on a single column/range of data that contains more than one text instance and loop the vlookup on that cells text. What I essentially want to do is to Vlookup each cell's text entry word by word. So if the cell contains say 3 words "Hat Jumper Teeth" I want Vlookup to first look up the word 'Hat', then look up the word 'Jumper' and lastly the word 'Teeth' and when (if) if finds a match in another sheet, to return what that match is. Ideally when its found a match it will stop looking at the other words in that cell.

The purpose of the exercise is I'm trying to categorise a vast list of product data into a small set of sub categories based on product style. This way I can then group products into a range which will make my life a whole lot easier with managing my product catalogues!

Any ideas how this works? I tried a previously posted thread about the sunject but the code was slightly different and wanted to find blank entries. I also tried making the text to columns but this then gets very labor intensive as I'm dealing with many thousands of entries.

Thanks all!

 

[JackDanIce]

What is the name of the sheet the data is on with the text instances and what cell does this data start/end in?
Where is the data that the words are being lookup'd against? What cell does it start in, what column does it match to and where is the last cell?
Where should the match or output go?

Please be specific, only you can see your spreadsheet, total guesswork for anyone reading your post.

 

[Eric W]

Depending on your layout, and how many words in a cell you want to look at, here's a possibility:

	A	B	C	D	E	F	G
Hat Jumper Teeth	five	five	Couch	one
Ace King Queen			Arm	two
Mouse Couch Arm	ten	ten	Leg	three
Piano Elevator Mouse Car Banana	six	six	Teeth	four
Banana Car Mouse Elevator Piano	seven	seven	Hat	five
	Piano	six
	Car	seven
Nose	eight
Cat	nine
Mouse	ten

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]=IFERROR(INDEX($G$1:$G$10,LOOKUP(2^20,MATCH(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1-ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1)))*99+1,99)),$F$1:$F$10,0))),"")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C1[/TH]
[TD="align: left"]=IFERROR(INDEX($G$1:$G$10,LOOKUP(2^20,MATCH(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),(D1-ROW(INDIRECT("1:"&D1)))*99+1,99)),$F$1:$F$10,0))),"")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D1[/TH]
[TD="align: left"]=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Your Lookup table is in F1:G1, your list of words is in column A, and the formula in B1 will find the first matching word from the list. (If you're willing to use a helper column, the formulas in C1:D1 are a bit shorter.) Drag the formula down as needed.

Let us know if this helps.

 

[leeandoona]

What is the name of the sheet the data is on with the text instances and what cell does this data start/end in?
Where is the data that the words are being lookup'd against? What cell does it start in, what column does it match to and where is the last cell?
Where should the match or output go?

Please be specific, only you can see your spreadsheet, total guesswork for anyone reading your post.

Appologies, I was sounding out the possibilities rather than being specific.

Speficly then, the name of the sheet with the data on that contains the text instances is 'ProductList'. The data starts in A2 (A1 contains the column header 'Products'). This list is currently down to row 17030 (its a long list). It will though, keep growing as we are merging many files into one for simplification (ideally). The match/output can go anywhere, I propose in column B2 down. The data that the words are being looked up is on a seperate sheet on the same workbook. This sheet is titled 'ProductCategories'. The data to lookup is in Column A2 (A1 header is 'ProductKeyword') downwards and is currently to row 2013. The resulting data I would hope to return then is adjacent to this in column C2 (C1 Header is 'Category') downawards and is also down to row 2013. I have other relating data, namely In column B (header title 'ProductType') and in column D (header 'CategoryCode') and Column E (header 'ShipMeth').

As I say though, the main aspect of the work is I'm attempting to look up the data in 'ProductList' from A2 down and look within each cell at multi length text entries seperated by character spaces. I am hoping to loop down the column through each cell looking at each text word and finding a match over on the 'ProductCategories' sheet in Column A 'ProductKeyword' and then return the data held within column C 'Category' back to the sheet 'ProductList' and place it next to Column A in Column B when it finds a match. When it finds a match it can then stop looking for that whole cells contents and move on to the next cell and continue. When that's done I can then perform a standard vlookup from there to obtain other less inconsistent data I guess.

Thanks for taking the time to read all this btw! I did manage to get it to loop on some test data in VBA but when I put that macro into practice with this data it failed rather miserably!

 

[leeandoona]

Depending on your layout, and how many words in a cell you want to look at, here's a possibility:

	A	B	C	D	E	F	G
Hat Jumper Teeth	five	five	Couch	one
Ace King Queen			Arm	two
Mouse Couch Arm	ten	ten	Leg	three
Piano Elevator Mouse Car Banana	six	six	Teeth	four
Banana Car Mouse Elevator Piano	seven	seven	Hat	five
	Piano	six
	Car	seven
Nose	eight
Cat	nine
Mouse	ten

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]=IFERROR(INDEX($G$1:$G$10,LOOKUP(2^20,MATCH(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1-ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1)))*99+1,99)),$F$1:$F$10,0))),"")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C1[/TH]
[TD="align: left"]=IFERROR(INDEX($G$1:$G$10,LOOKUP(2^20,MATCH(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),(D1-ROW(INDIRECT("1:"&D1)))*99+1,99)),$F$1:$F$10,0))),"")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D1[/TH]
[TD="align: left"]=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Your Lookup table is in F1:G1, your list of words is in column A, and the formula in B1 will find the first matching word from the list. (If you're willing to use a helper column, the formulas in C1:D1 are a bit shorter.) Drag the formula down as needed.

Let us know if this helps.

Eric! This is AMAZING! Clever way of trying this, I never thought of it like that using a helper column might actually be a good idea! My data is over a few sheets. I'll try this now to see if I can make it work or I'll shuffle my data about to make it work and I'll report back how it went! Thanks so much for this great suggestion!

Lee

 

[Eric W]

Let me know how you fare. Since you seem to be fine with VBA, I could write a UDF that would probably be easier to use, and be more robust. But it does sound like I've got the right idea of what you want!

 

[leeandoona]

Eric! This is AMAZING! Clever way of trying this, I never thought of it like that using a helper column might actually be a good idea! My data is over a few sheets. I'll try this now to see if I can make it work or I'll shuffle my data about to make it work and I'll report back how it went! Thanks so much for this great suggestion!

Lee

Follow Up Eric. I've tried this a few ways now and it appears I have a limitation of some kind within my version of excel (2016) as I am presented with the following prompt!

"This formula uses more levels of nesting than you can use in the current file format".

This was done on the same worksheet as per your excellent example above. Just to double check I also tried to make this work between two sheets of data and I get the same message.

However when I use the helper column method that appears to work very efficiently indeed!

I'm okay with VBA provided I have something to follow as a guide. Ive been told my code is very messy! (self taught). Ultimately I'm working towards a much bigger 'mega-formula' which uses this kind of thing and then performs a number of Operations and Routines based on this first bit of work. I'll post that bigger bit of work here when I get it to work as it may be of use to someone who deals with a lot of text data rather than numerical data.

In the meantime, I'm going to apply the helper column method which I'm dubbing the 'Eric Help Method' to the entire dataset and then take a closer look to make sure I'm looking at the right data! So far though on the test data it looks brilliant!!!

Lee

 

[Eric W]

Hi Lee,

It's odd that you get that message. I haven't seen it, and I can only assume that it's a result of a larger data set. I'm also somewhat surprised that the 'Eric helper method' works, when the single cell formula doesn't. I wouldn't think that difference would be enough to matter, but I guess it does.

In any event, I wrote up a UDF, that might work if you still have problems. If needed, try this:

1) Open a copy of your workbook
2) Press Alt-F11 to open the VBA editor
3) Either use an existing module sheet, or add one with Insert > Module
4) Paste this code:

Public Function SeqVlookup(ByVal target As String, vrange As Range, vcol As Long)
Dim Words As Variant, w As Variant

    Words = Split(target, " ")
    
    On Error Resume Next
    For Each w In Words
        SeqVlookup = WorksheetFunction.VLookup(Trim(w), vrange, vcol, False)
        If SeqVlookup <> "" Then Exit Function
    Next w
    
    SeqVlookup = "No match"
    
End Function

5) Go back to your Excel workbook and try this formula:

=SeqVlookup(A1, $F$1:$G$10, 2)

This works fine with multiple sheets as well if you add the sheet reference:

=SeqVlookup(Sheet1!A1, Sheet2!$F$1:$G$2, 2)

Good luck!

 

[leeandoona]

Hi Lee,

It's odd that you get that message. I haven't seen it, and I can only assume that it's a result of a larger data set. I'm also somewhat surprised that the 'Eric helper method' works, when the single cell formula doesn't. I wouldn't think that difference would be enough to matter, but I guess it does.

In any event, I wrote up a UDF, that might work if you still have problems. If needed, try this:

1) Open a copy of your workbook
2) Press Alt-F11 to open the VBA editor
3) Either use an existing module sheet, or add one with Insert > Module
4) Paste this code:

Public Function SeqVlookup(ByVal target As String, vrange As Range, vcol As Long)
Dim Words As Variant, w As Variant

    Words = Split(target, " ")
    
    On Error Resume Next
    For Each w In Words
        SeqVlookup = WorksheetFunction.VLookup(Trim(w), vrange, vcol, False)
        If SeqVlookup <> "" Then Exit Function
    Next w
    
    SeqVlookup = "No match"
    
End Function

5) Go back to your Excel workbook and try this formula:

=SeqVlookup(A1, $F$1:$G$10, 2)

This works fine with multiple sheets as well if you add the sheet reference:

=SeqVlookup(Sheet1!A1, Sheet2!$F$1:$G$2, 2)

Good luck!

Hi Eric. WOW Thanks for that, I'll try that this afternoon and see how it works and report back. Yes its odd that it wouldn't work using the single function without the helper column. I guess it will be a more efficient way of carrying this out with large amounts of data, although it is reasonably quick for a few thoushand lines, I'm trying next on some 60,000 lines which may be a challenge for it! So far though using the excel Eric Help Method trick I've found it works well but I will try the code version as I'd prefer to put it all together that way.

As a side note. I've noticed a few exceptions where I've needed to adjust my data to pick up plurals "Cat" "Cats". I've also noted that I have some data that has been bracketed like "Cat (Hat)" and of course anything in a bracket is just ignored. I've also had to remember hyphenated words also get missed "Cat-Whiskers" but these are all just data specific and not problems, just thought I'd highlight these for anyone else using this info.

I'll post later how I get along with the above method.

Cheers

Lee

 

[Eric W]

Hello again,

If you are worried about performance, you might want to get rid of both the "Eric Help" function and the UDF. I could write a macro that you would call when you need it. It would read down your column and fill in the matches.

And you've come across one common issue: data that almost matches. If that's an issue to you beyond cleaning your data, I can point you to some links about fuzzy matching. I've never personally tried to build something.