Searching two lists by looking for partial string matches

[Chris d'Alchemi]

Hello,

Excel 2003 formula question, sorry if this has been asked earlier, but I haven't been able to find a match to my question.

I'm working from a string of many different elements and trying to find a match with another string of many different elements. If there is a matching element between the two, generate a preset number. Here goes the example, each list is one cell:

Client string list: Al, Cr, Zn, TSS, Oil and Grease, pH, Cyanide

Billing string list: Ag, Al, As, B, Ba, Be, Cd, Co, Cr, Cu, Fe, Hg, Mn, Mo, Ni, Pb, Se, Sn, Sr, Ti, V, Zn
​

Here's the idea: If any element within the client string list matches up with the billing string list, charge them $$$. So I would get a hit on "Al" since that appears in both strings. I'd also get hits for Cr and Zn, but it'd be unkind to triple charge our customers.

I can see what I want to do in my head but can't visualize the formula clearly enough. I would think it should look similar to =if(search({client_string_list, billing_string_list}),fee)) except I'll have to stick in wildcards everywhere.

There are many clients, each with a different list of things they want, and a list of possible tests, each with a series of components and fee. I've got my columns named and now I just need an approach.

Ideally, I would also have a lookup function in a combo box, so I could prepare a list of fees for each test by dropping in the particular client, but I'd like to solve the above first!

Thanks in advance

 

[AnAnalyst]

Hi there,

Your application actually seems like a relational database would be more appropriate. Have you thought about doing this in Access or something similar?

Also, I'm unclear as to your charging structure. Do you want fees to be based on individual determinands, set suites or some other kind of structure?

Will clients want to change their minds in the future regarding the test suite they require?

How is the test suite list formatted for each client? (Single cell per test or something else?)

All the best

 

[Aladin Akyurek]

If each string is in a cell of its own...

=ISNUMBER(LOOKUP(9.99999999999999E+307,MATCH(ClientList,BillingList,0)))*Fee

 

[Chris d'Alchemi]

Hello again, many thx for responding,

Access is a bit later down the road for me in my personal training. We don't have any applications in the workplace that use it, hence not something to push.

I'm probably not very clear in many ways. I hope here are better examples:

Data from the client's columns. Each row is a client's request:
<TABLE style="WIDTH: 559pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=745 border=0 x:str><COLGROUP><COL style="WIDTH: 559pt; mso-width-source: userset; mso-width-alt: 27245" width=745><TBODY><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: #ffff99; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; mso-ignore: style; mso-pattern: auto none" width=745 height=16 x:str="TSS, Oil and Grease, Cu, Fe, ">TSS, Oil and Grease, Cu, Fe, </TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: #ffff99; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; mso-ignore: style; mso-pattern: auto none" width=745 height=16>Cyanide, Cr</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: #ffff99; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; mso-ignore: style; mso-pattern: auto none" width=745 height=16>Cu, Fe, TSS, Oil and Grease, cBOD</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: #99cc00; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; mso-ignore: style; mso-pattern: auto none" width=745 height=16>Oil and Grease, Cyanide, Phenol, (200.9 - Sb, As, Be, Cd, Cu, Pb, Ni, Se, Ag, Tl) (200.7 - Al, Co, Mo, V, Ba, Cr, Fe, Mn, Zn), Hg, Cr+6</TD></TR></TBODY></TABLE>

From the billing fee list. Each row represents a test with everything we can do (the first one, for example, is a trace metals test)
<TABLE style="WIDTH: 382pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=509 border=0 x:str><COLGROUP><COL style="WIDTH: 382pt; mso-width-source: userset; mso-width-alt: 18614" width=509><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 382pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=509 height=17>Ag, Al, As, B, Ba, Be, Cd, Co, Cr, Cu, Fe, Hg, Mn, Mo, Ni, Pb, Se, Sn, Sr, Ti, V, Zn</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Cr+6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>BOD, cBOD, COD, TOC</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Alkalinity, Br, Ca, Cl, F, Hardness, K, Mg, Na, Conductivity, SO4</TD></TR></TBODY></TABLE>

So if a client wants Fe and BOD (as one does above), I'll get a hit from the Trace Metals group (for the iron) and the Demand group (for the BOD). I'd like to get a sum of the fees from those two groups.

I think what I need to do is extract out an exact match of "Fe" from both lists; if this is true, then apply fee. I'll have to figure out what to do if there is a second hit from the same list, though...

Again, thanks for taking a look.