Find first matching substring in a list and return adjacent value from another list

[chris32]

Hi there,

I am trying to find a way to harmonise the way a product is coded from various ways people may have entered it (i.e., the cut of fish - in parentheses here but note that this is not always the case).
For example, in the sample data below, "Gutted" and "whole gutted" mean the same thing, but "headed & gutted" is a different thing, and "whole" alone is yet a different thing.

TAB "Raw data", column A:

I am trying to automate the translation of these multiple possible terms people may use into a standard state code, by means of a formula in column B.
I have created a list of possible ways to name various cuts of fish (i.e. StateName) in a different tab "States translation" (column A), and the corresponding standard code (StateCode) next to it (column B).

TAB "States translation", column C and D:

I have made these two lists into arrays that I have named "StateName" and "StateCode" and that I would like to refer to in the tab where my raw data will be.
As you see in the two columns above, different states may include the same terms (e.g., gutted is found in GGT, GHCO, HGT and HGU) and so I have organised my list of possible terms in a hierarchical order, so that the search should end when the first matching StateName is found withing the string in column D.

So for example, for the fifth term in column A, "Broadbill Swordfish (Headed & Gutted)", I'd like the formula to return HGU (rather than any of the codes above).

I have tried various methods (lookup, index, match) unsuccessfully as they either find multiple matches or result in lists of the same length as my arrays.

I have a very manual and inelegant solution to this problem, using IFS, ISNUMBER and SEARCH, but I am sure there must be a much more straightforward way than this:

=IF(D2<>"",IFS(ISNUMBER(SEARCH('States translation'!$C$2,D2)),'States translation'!$D$2,ISNUMBER(SEARCH('States translation'!$C$3,D2)),'States translation'!$D$3,ISNUMBER(SEARCH('States translation'!$C$4,D2)),'States translation'!$D$4,ISNUMBER(SEARCH('States translation'!$C$5,D2)),'States translation'!$D$5, [repeat same arguments with the entire list of 180+ items ] , TRUE,"Check"),"")

Many thanks for your help!

PS: Note that more state names could always be added to my arrays later on if it turn out that people have used names that were not covered in my current lists.

 

[chris32]

Sorry, I got my column names mixed up (in my file, column A in tab "Raw data" is actually column D too.

Here's how it is organised:

 

[Peter_SSs]

Welcome to the MrExcel board!

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are without a mass of manual typing from images like that. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[chris32]

Welcome to the MrExcel board!

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are without a mass of manual typing from images like that. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Hi Peter,

thank you for the tip. I am not on a personal computer unfortunately, and can't add the add-in.

If it is of any help, here's a text version of the data sample (I have used semicolon as separators so that the data can be copy-pasted and separated into an excel sheet).
Any help is very much appreciated as I realised my inelegant solution won't work because it exceeds the maximum number of characters that excel allows within a cell!

Tab "raw data":
Product ;State
School Shark (Skinless Boneless Fillets) ;<formula with result>
Groper (Headed & Gutted) ;<formula with result>
Trumpeter (Headed & Gutted) ;<formula with result>
Ling (Dressed) ;<formula with result>
Trumpeter (Headed & Gutted) ;<formula with result>
Chilled Ling (Dressed) ;<formula with result>
School Shark (Dressed) ;<formula with result>
Groper (Headed & Gutted) ;<formula with result>
BlueCod (Whole Gutted) ;<formula with result>
Trumpeter (Headed & Gutted) ;<formula with result>
Bluenose (Whole) ;<formula with result>
Rig (Dressed) ;<formula with result>
Chilled Groper (Headed & Gutted) ;<formula with result>
Chilled Ling (Dressed) ;<formula with result>
Chilled Trumpeter (Headed & Gutted) ;<formula with result>
Chilled Ling (Dressed) ;<formula with result>
Chilled Trumpeter (Headed & Gutted) ;<formula with result>
Ling (Dressed) ;<formula with result>
Ling (Dressed) ;<formula with result>
Sandflounder (Whole Gutted) ;<formula with result>
Yellowbelly (Whole Gutted) ;<formula with result>
Chilled Yellowbelly (Whole Gutted) ;<formula with result>
Chilled Broadbill Swordfish (Headed & Gutted) ;<formula with result>
School Shark (Dressed) ;<formula with result>
Trumpeter (Headed & Gutted) ;<formula with result>
Bluenose (Whole) ;<formula with result>

Tab "States translation":
StateName; StateCode
gilled gutted tail-off; GGT
gilled, gutted, tail-off; GGT
gilled gutted tail off; GGT
gilled, gutted, tail off; GGT
gilled and gutted tail off; GGT
gilled and gutted, tail off; GGT
gilled & gutted tail off; GGT
gilled & gutted, tail off; GGT
gilled & gutted tail-off; GGT
gilled & gutted, tail-off; GGT
gilled, gutted, tailed; GGT
gilled gutted tailed; GGT
gilled and gutted, tailed; GGT
gilled & gutted, tailed; GGT
gilled and gutted; GGO
gilled & gutted; GGO
gilled gutted; GGO
gilled gutted tail-on; GGO
gilled, gutted, tail-on; GGO
gilled gutted tail on; GGO
gilled, gutted, tail on; GGO
headed gutted tailed; HGT
headed, gutted, tailed; HGT
headed and gutted, tailed; HGT
headed & gutted, tailed; HGT
headed and gutted tailed; HGT
headed & gutted tailed; HGT
headed gutted tail off; HGT
headed gutted tail-off; HGT
headed, gutted, tail off; HGT
headed, gutted, tail-off; HGT
headed & gutted; HGU
headed &gutted; HGU
headed& gutted; HGU
headed&gutted; HGU
headed and gutted; HGU
headed gutted; HGU
dressed-straight cut; DSC
dressed, straight cut; DSC
dressed straight cut; DSC
dressed - strait cut; DSC
straight cut; DSC
dressed-v cut; DVC
dressed, v cut; DVC
dressed v cut; DVC
dressed - v cut; DVC
v cut; DVC
dressed; DRE
skin-on untrimmed fillet; UTF
skin on untrimmed fillet; UTF
untrimmed fillet; UTF
untrimmed fillet skin on; UTF
untrimmed fillets skin on; UTF
untrimmed fillet skin-on; UTF
untrimmed fillets skin-on; UTF
skin-on trimmed fillet; TRF
skin on trimmed fillet; TRF
trimmed fillet; TRF
trimmed fillet skin on; TRF
trimmed fillets skin on; TRF
trimmed fillet skin-on; TRF
trimmed fillets skin-on; TRF
skin-off untrimmed fillet; USK
skin off untrimmed fillet; USK
untrimmed fillet skin off; USK
untrimmed fillets skin off; USK
untrimmed fillet skin-off; USK
untrimmed fillets skin-off; USK
skinned untrimmed fillet; USK
skinned untrimmed fillet; USK
skinless untrimmed fillet; USK
untrimmed fillet skinless; USK
skin-off trimmed fillet; TSK
skin off trimmed fillet; TSK
trimmed fillet skin off; TSK
trimmed fillets skin off; TSK
trimmed fillet skin-off; TSK
trimmed fillets skin-off; TSK
skinned trimmed fillet; TSK
skinned trimmed fillet; TSK
skinless trimmed fillet; TSK
trimmed fillet skinless; TSK

 

[Peter_SSs]

If it is of any help, here's a text version of the data ..

Yes, that helps greatly, thanks.

Is this what you are trying to do?
I am assuming the "Not Found" results arise from not having the entire data from 'States translation'

chris32.xlsm
	C	D
1	Product	State
2	School Shark (Skinless Boneless Fillets)	Not Found
3	Groper (Headed & Gutted)	HGU
4	Trumpeter (Headed & Gutted)	HGU
5	Ling (Dressed)	DRE
6	Trumpeter (Headed & Gutted)	HGU
7	Chilled Ling (Dressed)	DRE
8	School Shark (Dressed)	DRE
9	Groper (Headed & Gutted)	HGU
10	BlueCod (Whole Gutted)	Not Found
11	Trumpeter (Headed & Gutted)	HGU
12	Bluenose (Whole)	Not Found
13	Rig (Dressed)	DRE
14	Chilled Groper (Headed & Gutted)	HGU
15	Chilled Ling (Dressed)	DRE
16	Chilled Trumpeter (Headed & Gutted)	HGU
17	Chilled Ling (Dressed)	DRE
18	Chilled Trumpeter (Headed & Gutted)	HGU
19	Ling (Dressed)	DRE
20	Ling (Dressed)	DRE
21	Sandflounder (Whole Gutted)	Not Found
22	Yellowbelly (Whole Gutted)	Not Found
23	Chilled Yellowbelly (Whole Gutted)	Not Found
24	Chilled Broadbill Swordfish (Headed & Gutted)	HGU
25	School Shark (Dressed)	DRE
26	Trumpeter (Headed & Gutted)	HGU
27	Bluenose (Whole)	Not Found
Raw data

Cell Formulas
Range	Formula
D2:D27	D2	=IFNA(VLOOKUP(REPLACE(LEFT(C2,LEN(C2)-1),1,FIND("(",C2),""),'States translation'!C$2:D$83,2,0),"Not Found")

 

[Jake975]

Maybe this will help if you modify it to your data and what you need returned.

   Dim rng As Range, WS1 As Worksheet, WS2 As Worksheet, desWS As Worksheet, fnd As Range
    Set WS1 = Sheets("Temp")
    Set WS2 = Sheets("Data List")
For Each rng In WS2.Range("C2", WS2.Range("C" & WS2.Rows.Count).End(xlUp))
        Set fnd = WS1.Range("A:A").Find(rng, LookIn:=xlValues, lookat:=xlPart)
        If Not fnd Is Nothing Then
            fnd.Offset(, 1) = rng.Offset(, 1)
        End If
    Next rng

 

[chris32]

Hi Peter,

thank you for the formula! I have just added $ signs so that it searches in the same array when I drag the formula down.

=IFNA(VLOOKUP(REPLACE(LEFT(D35,LEN(D35)-1),1,FIND("(",D35),""),'States translation'!$C$2:$D$200,2,0),"Not Found")

It works in most instances but not every time. I think it is because it relies on finding the closing parenthesis and then the next letter to the left from there, is that right?

Some entries returned a "Not found" when the states were in plural but in singular in my list (e.g., "Chilled Sea Perch (Skinned and Boned Fillets)" returned a "Not found" even if "Skinned and Boned Fillet" is in my list). I could solve this by pluralising terms in my list. However, some entries do not contain parentheses (e.g., "Frozen Tuna Whole") , in which case I've got a #VALUE! error.

One case I don't get is with this single search term "gutted":

"Butterfish (Gutted)" returns a "Not found" even if "Gutted" is in my list. It is at the bottom of my list because there are many other states that contain "gutted" associated with other words, like so:
fillet skin on; FIL
fillets skin on; FIL
fillets skin-on; FIL
fillet skin on; FIL
headed, gutted and finned; HGF
headed, gutted & finned; HGF
headed gutted finned; HGF
headed gutted and finned; HGF
headed gutted & finned; HGF
headed and gutted, finned; HGF
headed & gutted, finned; HGF
headed and gutted finned; HGF
headed & gutted finned; HGF
whole gutted; GUT
Gutted; GUT
whole; GRE
green; GRE

Is there a way to not rely on finding the parenthesis and independently searching for the substring?

Many thanks for your help!

Yes, that helps greatly, thanks.

Is this what you are trying to do?
I am assuming the "Not Found" results arise from not having the entire data from 'States translation'

 

[chris32]

Maybe this will help if you modify it to your data and what you need returned.

   Dim rng As Range, WS1 As Worksheet, WS2 As Worksheet, desWS As Worksheet, fnd As Range
    Set WS1 = Sheets("Temp")
    Set WS2 = Sheets("Data List")
For Each rng In WS2.Range("C2", WS2.Range("C" & WS2.Rows.Count).End(xlUp))
        Set fnd = WS1.Range("A:A").Find(rng, LookIn:=xlValues, lookat:=xlPart)
        If Not fnd Is Nothing Then
            fnd.Offset(, 1) = rng.Offset(, 1)
        End If
    Next rng

Hi Jake,

thank you. I am not great at VBA but will see if I can get somewhere with this code. Cheers!

 

[Peter_SSs]

I have just added $ signs so that it searches in the same array when I drag the formula down.

The $ signs that you added in from of the column letters will not hurt, but were not needed. Since the formula is being dragged down (not across) it is only the row numbers that need anchoring with $ signs and my formula already had $ signs before the row numbers. All you really needed to do was increase the 83 to 200.

it relies on finding the closing parenthesis and then the next letter to the left from there, is that right?

Correct

Some entries returned a "Not found" when the states were in plural but in singular in my list

We could accommodate that with

=IFNA(VLOOKUP(REPLACE(LEFT(D2,LEN(D2)-1),1,FIND("(",D2),""),'States translation'!C$2:D$200,2,0),IFNA(VLOOKUP(REPLACE(LEFT(D2,LEN(D2)-2),1,FIND("(",D2),""),'States translation'!C$2:D$200,2,0),"Not Found"))

One case I don't get is with this single search term "gutted":

Gutted works for me. Check that cell in 'States translation' and make sure that it does not have any trailing or leading spaces. Even just select that cell and type gutted again and see what happens.

Is there a way to not rely on finding the parenthesis and independently searching for the substring?

I don't see how you could logically decide what part of a multi-word string is the substring to look for if there is nothing to identify it?

 

[chris32]

One case I don't get is with this single search term "gutted":

"Butterfish (Gutted)" returns a "Not found" even if "Gutted" is in my list. It is at the bottom of my list because there are many other states that contain "gutted" associated with other words

Oh nevermind about that one Peter, it was my bad - there was an extra space at the end of the word "gutted" in my list - sorry about that!

Still very keen to know if there is a search function that would not rely on finding the parenthesis (had another case where the person entered "xxx ( whole gutted)" with an extra space after the parenthesis and so it did not find the matching state.

Thank you!