Check for match comma separated values in multiple cells

[Howard_Roark]

Hi,
I have always been able to figure out how to solve an excel problem by looking at forums but this one is impossible for me to figure out, so please help.

I have an excel sheet where I need to show which dog food is appropriate for which specific dog breed.
I have a table for pet breeds and another table with the pet food.
I need the column D, that is for each product, to be populated with all the breeds in one line (one cell), separated by columns.

The match is supposed to be based on size (XS, S, M, L, XL) and white color (yes or no).
Therefore, if any of the values separated by commas in cell B1 is present in a row of column H and C1 matches with column G at the same row, return from that row the value in column F. Run for each row for the D column in the product sheet.

Attached is an example of the Sheet.

I can also reconstruct the sheet if necessary.

Thank you

 

[James006]

Hi,

Very interesting Challenge which will excite ALL contributors ...!!! Congratulations !!!

My personal inclination would be to design a tailor-made UDF ... especially since you do need to concatenate all "compatible" breeds in Column D ...

 

[Howard_Roark]

Hi,

Very interesting Challenge which will excite ALL contributors ...!!! Congratulations !!!

My personal inclination would be to design a tailor-made UDF ... especially since you do need to concatenate all "compatible" breeds in Column D ...

I am also afraid that UDF would be the only way to go. This is above my skill level.

The concatenation is the only part I was able to figure out by using the =TEXTJOIN. Tried it with =IF function but it doesn't work properly. The challenge is the correct matching mechanism with the commas and returning of the appropriate cell value and applied for the whole table.

BTW: ChatGPT also could't figure it out...

 

[James006]

BTW: ChatGPT also could't figure it out...

The poor ChatGPT ...

It is Work-In-Progress ... You should be waiting for Version 5 ( availability Q4-2023 )

Rumor has it it will be a really major improvement

 

[James006]

Since you have tackled your own solution with a macro, both your columns B and H will require you to use the VBA Split function ...

VBA Split Function - Split String of Text into Array - Automate Excel

In this ArticleVBA Split FunctionUsing the Split Function with a Delimiter CharacterUsing a Limit Parameter in a Split FunctionUsing the Compare Parameter in a Split FunctionUsing Non-Printable Characters as the Delimiter CharacterUsing the Join Function to Reverse a SplitUsing the Split...

www.automateexcel.com

 

[kevin9999]

See if this comes close to what you're looking for. Put the Function in a standard module. Note the use of it in both a table & range below the code. Basically, it works like this:

=Woof(<cells to the left>,<table or range to the right absolute references>)

Function Woof(a, b As Range) As String
    Application.Volatile
    Dim i As Long, j As Long, Prods, Dogs As String
    Prods = Split(CStr(a(2)), ", ")
    Dim c: c = b
    For i = LBound(c, 1) To UBound(c, 1)
        For j = LBound(Prods) To UBound(Prods)
            If InStr(CStr(c(i, 3)), CStr(Prods(j))) And c(i, 2) = a(3) Then
                If Dogs = "" Then
                    Dogs = c(i, 1)
                Else
                    Dogs = Dogs & ", " & c(i, 1)
                End If
                Exit For
            End If
        Next j
    Next i
    Woof = Dogs
End Function

Woof.xlsm
	A	B	C	D	E	F	G	H
1	Product Name	Appropriate Breed Sizes	Appropriate for White Color	Appropriate for these Breeds		Breed	White Color	Breed Size
2	Product A	S, XS, M, L	Yes	Affenpinscher, Africanis, Airedale Terrier, Akita, Alano Espanol		Affenpinscher	Yes	L, XL
3	Product B	M, L, XL	No	Afghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood Bulldog		Afghan Hound	No	L, XL
4	Product C	S, M, L	Yes	Affenpinscher, Africanis, Airedale Terrier, Akita, Alano Espanol		Africanis	Yes	L, XL
5	Product D	L, XL	No	Afghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood Bulldog		Aidi	No	L, XL
6	Product E	L, M, XL	Yes	Affenpinscher, Africanis, Airedale Terrier, Akita		Airedale Terrier	Yes	L, XL
7	Product F	M, L, XL	No	Afghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood Bulldog		Akbash	No	L, XL
8	Product G	M, L, XL	Yes	Affenpinscher, Africanis, Airedale Terrier, Akita		Akita	Yes	M
9	Product H	L, M, XL	No	Afghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood Bulldog		Aksaray Malaklisi	No	XL
10	Product I	M, L, XL	Yes	Affenpinscher, Africanis, Airedale Terrier, Akita		Alano Espanol	Yes	S
11	Product J	M, L	No	Afghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood Bulldog		Alapaha Blue Blood Bulldog	No	XL
12
13
14	Product Name	Appropriate Breed Sizes	Appropriate for White Color	Appropriate for these Breeds		Breed	White Color	Breed Size
15	Product A	S, XS, M, L	Yes	Affenpinscher, Africanis, Airedale Terrier, Akita, Alano Espanol		Affenpinscher	Yes	L, XL
16	Product B	M, L, XL	No	Afghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood Bulldog		Afghan Hound	No	L, XL
17	Product C	S, M, L	Yes	Affenpinscher, Africanis, Airedale Terrier, Akita, Alano Espanol		Africanis	Yes	L, XL
18	Product D	L, XL	No	Afghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood Bulldog		Aidi	No	L, XL
19	Product E	L, M, XL	Yes	Affenpinscher, Africanis, Airedale Terrier, Akita		Airedale Terrier	Yes	L, XL
20	Product F	M, L, XL	No	Afghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood Bulldog		Akbash	No	L, XL
21	Product G	M, L, XL	Yes	Affenpinscher, Africanis, Airedale Terrier, Akita		Akita	Yes	M
22	Product H	L, M, XL	No	Afghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood Bulldog		Aksaray Malaklisi	No	XL
23	Product I	M, L, XL	Yes	Affenpinscher, Africanis, Airedale Terrier, Akita		Alano Espanol	Yes	S
24	Product J	M, L	No	Afghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood Bulldog		Alapaha Blue Blood Bulldog	No	XL
Sheet1

Cell Formulas
Range	Formula
D2:D11	D2	=Woof(Table1[@[Product Name]:[Appropriate for White Color]],Table2)
D15:D24	D15	=Woof(A15:C15,$F$2:$H$11)

 

[Howard_Roark]

I was able to figure it out manually. I am sure the is a more elegant solution that would implement the first (intermediate) step into the second step:

1) I split the sizes column into individual columns:
=TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",LEN($A2))),(J$1-1)*LEN($A2)+1,LEN($A2)))

Where J1 has "1" and K1 has "2" and so on. I did the same for both columns with sizes.

2) I checked all the columns with the sizes agains each other if they match and if white color matches and combined all the result into one line separated by comma:
=TEXTJOIN(", ",TRUE,FILTER(Table1[Breed],(IF(R2="",0,(Table1[1]=R2)+(Table1[2]=R2)+(Table1[3]=R2))+IF(S2="",0,(Table1[1]=S2)+(Table1[2]=S2)+(Table1[3]=S2))+IF(T2="",0,(Table1[1]=T2)+(Table1[2]=T2)+(Table1[3]=T2))+IF(U2="",0,(Table1[1]=U2)+(Table1[2]=U2)+(Table1[3]=U2))+IF(V2="",0,(Table1[1]=V2)+(Table1[2]=V2)+(Table1[3]=V2)))*(Table1[White Color]=Table2[@White Color]),""))

seems to work

 

[James006]

Hi,

Kevin's solution ... if I may underline it, is far more elegant, far more efficient and far less likely to be disrupted ...

 

[Fluff]

Another option without adding the extra columns.

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Product Name	Appropriate Breed Sizes	Appropriate for White Color	Appropriate for these Breeds		Breed	White Color	Breed Size
2	Product A	S, XS, M, L	Yes	Affenpinscher, Africanis, Airedale Terrier, Akita, Alano Espanol		Affenpinscher	Yes	L, XL
3	Product B	M, L, XL	No	Afghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood Bulldog		Afghan Hound	No	L, XL
4	Product C	S, M, L	Yes	Affenpinscher, Africanis, Airedale Terrier, Akita, Alano Espanol		Africanis	Yes	L, XL
5	Product D	L, XL	No	Afghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood Bulldog		Aidi	No	L, XL
6	Product E	L, M, XL	Yes	Affenpinscher, Africanis, Airedale Terrier, Akita		Airedale Terrier	Yes	L, XL
7	Product F	M, L, XL	No	Afghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood Bulldog		Akbash	No	L, XL
8	Product G	M, L, XL	Yes	Affenpinscher, Africanis, Airedale Terrier, Akita		Akita	Yes	M
9	Product H	L, M, XL	No	Afghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood Bulldog		Aksaray Malaklisi	No	XL
10	Product I	M, L, XL	Yes	Affenpinscher, Africanis, Airedale Terrier, Akita		Alano Espanol	Yes	S
11	Product J	M, L	No	Afghan Hound, Aidi, Akbash		Alapaha Blue Blood Bulldog	No	XL
Summary

Cell Formulas
Range	Formula
D2:D11	D2	=LET(s,TRIM(MID(SUBSTITUTE(B2,",",REPT(" ",100)),SEQUENCE(10,,,100),100)),f,TRANSPOSE(FILTER(" "&s&",",s<>"")),TEXTJOIN(", ",,FILTER($F$2:$F$11,(MMULT(SIGN(ISNUMBER(SEARCH(f," "&$H$2:$H$11&","))),SEQUENCE(COLUMNS(f),,,0)))*($G$2:$G$11=C2))))

 

[Howard_Roark]

Another option without adding the extra columns.

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Product Name	Appropriate Breed Sizes	Appropriate for White Color	Appropriate for these Breeds		Breed	White Color	Breed Size
2	Product A	S, XS, M, L	Yes	Affenpinscher, Africanis, Airedale Terrier, Akita, Alano Espanol		Affenpinscher	Yes	L, XL
3	Product B	M, L, XL	No	Afghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood Bulldog		Afghan Hound	No	L, XL
4	Product C	S, M, L	Yes	Affenpinscher, Africanis, Airedale Terrier, Akita, Alano Espanol		Africanis	Yes	L, XL
5	Product D	L, XL	No	Afghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood Bulldog		Aidi	No	L, XL
6	Product E	L, M, XL	Yes	Affenpinscher, Africanis, Airedale Terrier, Akita		Airedale Terrier	Yes	L, XL
7	Product F	M, L, XL	No	Afghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood Bulldog		Akbash	No	L, XL
8	Product G	M, L, XL	Yes	Affenpinscher, Africanis, Airedale Terrier, Akita		Akita	Yes	M
9	Product H	L, M, XL	No	Afghan Hound, Aidi, Akbash, Aksaray Malaklisi, Alapaha Blue Blood Bulldog		Aksaray Malaklisi	No	XL
10	Product I	M, L, XL	Yes	Affenpinscher, Africanis, Airedale Terrier, Akita		Alano Espanol	Yes	S
11	Product J	M, L	No	Afghan Hound, Aidi, Akbash		Alapaha Blue Blood Bulldog	No	XL
Summary

Cell Formulas
Range	Formula
D2:D11	D2	=LET(s,TRIM(MID(SUBSTITUTE(B2,",",REPT(" ",100)),SEQUENCE(10,,,100),100)),f,TRANSPOSE(FILTER(" "&s&",",s<>"")),TEXTJOIN(", ",,FILTER($F$2:$F$11,(MMULT(SIGN(ISNUMBER(SEARCH(f," "&$H$2:$H$11&","))),SEQUENCE(COLUMNS(f),,,0)))*($G$2:$G$11=C2))))

Works beautifully