Evaluate content based on multiple criteria.

BillTony · Sep 14, 2023

I'm trying to find "matches" (loosely speaking...) in a 3 column table and displaying the results in a 4th column. Column A is the driver, and can hold up to 5 pre-defined values, or any COMBINATION of the 5 values. Columns B and C cannot hold any of the values found in Column A, and again, can hold 1 or multiple values. I have not included any coding as I have not yet found anything that works. I'm attaching an image that should be a pretty good representation of what I wish to accomplish
ANY HELP IS GREATLY APPRECIATED!

Brunts · Sep 15, 2023

What matching are you actually wanting:-
- If a value in Cust_2 or Cust_3 contains one or more values from the Cust_1 column for the row in question, then count that as a match
OR
- If a value in Cust_2 or Cust_3 contains all the values from the Cust_1 column for the row in question, count that as a match

What values are you wanting to see in the Result column (Other than not "Error"):-
- If there is a whole or partial match in either column - insert the word "Match" in the Result column? If not, what are you wanting? The actual matching values, but not from which column

- A match between Cust 2 and Cust_1 or a match between Cust_3 and Cust_1
- e.g. Cust_2 matches with Val 1 but Cust_3 matches with Val 2 - what are you wanting in the Result column - the matching values or just the word "Match" in the Result column?
- Should columns containing matching values be highlighted in red

The red colour appears to confirm a "partial" or "whole" match between columns but if they are different matching values between Cust_2 and Cust_3 and Cust_1 - all values just show as red and you are no further forward in identifying the actual matching values.

BillTony · Sep 15, 2023

Thanks for the reply! Let's look at it this way (a single row at a time): If column A has ANY of the following values, either singly or a combination of any or all - A, B, C, D, or E - then neither column B nor C could contain that exact value. But NOT an EXACT match. For example if column A has A, B, E then both columns B and C can contain ANY of those values. I'm not looking for a "match" of the "string" found in column A, but rather a comparison or evaluation of the VALUES columns B and C. In other words, columns B and C cannot contain ANYTHING found in column A (other than a comma if more than one value is held in any of the columns). So again, perhaps "matching" is misleading. I am not looking to identify or specify any of the "like values," only that they are PRESENT. In that scenario I would have something like "FAIL" (a match or partial match) / "PASS" (no match of any value), etc. I do intend to use color coding / shading to identify WHERE the "errors" are found in each column, but that is more as a visual aid for the end user to see where and in which column the error is occurring. I hope this makes things a little clearer. Thanks again for the input!

Brunts · Sep 16, 2023

Two parts contradict things in my mind:-
- If column A has ANY of the following values, either singly or a combination of any or all - A, B, C, D, or E - then neither column B nor C could contain that exact value. But NOT an EXACT match.
- columns B and C cannot contain ANYTHING found in column A

Going through your initial image to confirm the expected result:-
Excel Row 2 - as Val 1 is also contained in Cust_2 column then the expected Result is "FAIL"
Excel Row 3 - as Val 2 is also contained in Cust_3 column then the expected Result is "FAIL"
Excel Row 4 - as Val 3 is also contained in Cust_2 column then the expected Result is "FAIL"
Excel Row 5 - as Val 4 is also contained in Cust_3 column then the expected Result is "FAIL"
Excel Row 6 - as Val 5 is not in either Cust_2 or Cust_3 columns then the expected Result is "PASS"
Excel Row 7 - as Val 1 & Val 2 are also contained in Cust_2 column then the expected Result is "FAIL"
Excel Row 8 - as Val 1 & Val 2 are also contained in Cust_2 column then the expected Result is "FAIL"
Excel Row 9 - as Val 1 is also contained in Cust_3 column then the expected Result is "FAIL"
Excel Row 10 - as Val 1, Val 3 & Val 4 are also contained in Cust_2 and Cust_3 columns then the expected Result is "FAIL"
Excel Row 11 - as Val 3 or Val 5 are not contained in Cust_2 or Cust_3 columns then the expected Result is "PASS"
Excel Row 12 - as Val 1 and Val 3 are contained in Cust_2 or Cust_3 columns then the expected Result is "FAIL"

And then highlight the rows in the Cust_1, 2 & 3 columns that contain a "match" and therefore overall result is "FAIL"
Does that agree with your requirements?

BillTony · Sep 17, 2023

Thanks for the reply! Yes, the summary above is absolutely correct!

Brunts · Sep 17, 2023

Have solved with a load of additional columns and formulas - presumed you wanted formulas, rather than VB.

- Yellow columns are counting the number of values in the CUST_2 column/Row then confirming whether the values exist in the CUST_1 column, then counting the overall number of matches for CUST_2/CUST_1.
- Orange columns are counting the number of values in the CUST_3 column/Row then confirming whether the values exist in the CUST_1 column, then counting the overall number of matches for CUST_3/CUST_1.
- Conditional Formatting applied to CUST_1, CUST_2 and CUST_3 columns, so that:-
- CUST_2 column will have a yellow fill if there is a match between any values in CUST_1 and CUST_2 column / row
- CUST_3 column will have an orange fill if there is a match between any values in CUST_1 and CUST_3 column / row
- CUST_1 column, if there is only a match between CUST_1 and CUST_2 columns, then a yellow fill will be applied
- CUST_1 column, if there is only a match between CUST_1 and CUST_3 columns, then an orange fill will be applied
- CUST_1 column, if there is a match between CUST_1 and CUST_2 columns and CUST_1 and CUST_3 columns, then a red fill will be applied
- Formula inserted into the Results column confirming whether there are matches or not and outcome will be PASS / FAIL

(Hopefully I've followed the instructions properly to upload the excel solution with formulas etc)

Evaluate content based on multiple criteria.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

1

Row

CUST_1

CUST_2

CUST_3

Result

No of values in CUST_2 Column

Is first value in Cust_2 column in the Cust_1 Column

Is Second value in Cust_2 column in the Cust_1 Column

Is Third value in Cust_2 column in the Cust_1 Column

Is Fourth value in Cust_2 column in the Cust_1 Column

Is Fifth value in Cust_2 column in the Cust_1 Column

Count of Cust_2 column matches

No of values in CUST_3 Column

Is first value in Cust_3 column in the Cust_1 Column

Is Second value in Cust_3 column in the Cust_1 Column

Is Third value in Cust_3 column in the Cust_1 Column

Is Fourth value in Cust_3 column in the Cust_1 Column

Is Fifth value in Cust_3 column in the Cust_1 Column

Count of Cust_3 column matches

2

Val1

Val2, Val1

Val2, Val3

FAIL

as Val 1 is also contained in Cust_2 column then the expected Result is "FAIL"

3

Val2

Val3

Val2

FAIL

as Val 2 is also contained in Cust_3 column then the expected Result is "FAIL"

4

Val3

Val3, Val1

Val5

FAIL

as Val 3 is also contained in Cust_2 column then the expected Result is "FAIL"

5

Val4

Val1

Val4

FAIL

as Val 4 is also contained in Cust_3 column then the expected Result is "FAIL"

6

Val5

Val2, Val1

Val3, Val4

PASS

as Val 5 is not in either Cust_2 or Cust_3 columns then the expected Result is "PASS"

7

Val1, Val2

Val4

FAIL

as Val 1 & Val 2 are also contained in Cust_2 column then the expected Result is "FAIL"

8

Val1, Val2, Val3

Val2, Val1

Val5

FAIL

as Val 1 & Val 2 are also contained in Cust_2 column then the expected Result is "FAIL"

9

Val1, Val2, Val3, Val4

Val5

Val1

FAIL

as Val 1 is also contained in Cust_3 column then the expected Result is "FAIL"

10

Val1, Val2, Val3, Val4, Val5

Val1, Val4

Val3, Val4, Val5

FAIL

as Val 1, Val 3 & Val 4 are also contained in Cust_2 and Cust_3 columns then the expected Result is "FAIL"

11

Val3, Val5

Val1

Val4

PASS

as Val 3 or Val 5 are not contained in Cust_2 or Cust_3 columns then the expected Result is "PASS"

12

Val1, Val2, Val3

Val3

Val1

FAIL

as Val 1 and Val 3 are contained in Cust_2 or Cust_3 columns then the expected Result is "FAIL"

Sheet1

Cell Formulas
Range	Formula
G2:G12	G2	=LEN(C2)-LEN(SUBSTITUTE(C2,",",""))+1
H2:H12	H2	=IFERROR(IF(FIND(IFERROR(LEFT(C2,(FIND(",",C2,1)-1)),C2),$B2)>0,"MATCH","OK"),"OK")
I2:I12	I2	=IF(G2>=2,(IFERROR(IF(FIND(TRIM(MID(SUBSTITUTE(C2,",",REPT(" ",100)),100,100)),B2)>0,"MATCH","OK"),"OK")),"")
J2:J12	J2	=IF(G2>=3,(IFERROR(IF(FIND(TRIM(MID(SUBSTITUTE(C2,",",REPT(" ",100)),200,100)),B2)>0,"MATCH","OK"),"OK")),"")
K2:K12	K2	=IF(G2>=4,(IFERROR(IF(FIND(TRIM(MID(SUBSTITUTE(C2,",",REPT(" ",100)),300,100)),B2)>0,"MATCH","OK"),"OK")),"")
L2:L12	L2	=IF(G2>=5,(IFERROR(IF(FIND(TRIM(MID(SUBSTITUTE(C2,",",REPT(" ",100)),400,100)),B2)>0,"MATCH","OK"),"OK")),"")
M2:M12,T2:T12	M2	=COUNTIF(H2:L2,"MATCH")
N2:N12	N2	=LEN(D2)-LEN(SUBSTITUTE(D2,",",""))+1
O2:O12	O2	=IFERROR(IF(FIND(IFERROR(LEFT(D2,(FIND(",",D2,1)-1)),D2),$B2)>0,"MATCH","OK"),"OK")
P2:P12	P2	=IF(N2>=2,(IFERROR(IF(FIND(TRIM(MID(SUBSTITUTE(D2,",",REPT(" ",100)),100,100)),B2)>0,"MATCH","OK"),"OK")),"")
Q2:Q12	Q2	=IF(N2>=3,(IFERROR(IF(FIND(TRIM(MID(SUBSTITUTE(D2,",",REPT(" ",100)),200,100)),B2)>0,"MATCH","OK"),"OK")),"")
R2:R12	R2	=IF(N2>=4,(IFERROR(IF(FIND(TRIM(MID(SUBSTITUTE(D2,",",REPT(" ",100)),300,100)),B2)>0,"MATCH","OK"),"OK")),"")
S2:S12	S2	=IF(N2>=5,(IFERROR(IF(FIND(TRIM(MID(SUBSTITUTE(D2,",",REPT(" ",100)),400,100)),B2)>0,"MATCH","OK"),"OK")),"")
E2:E12	E2	=IF(OR(M2>0,T2>0),"FAIL","PASS")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D12	Expression	=T2>0	text	NO
C2:C12	Expression	=M2>0	text	NO
B2:B12	Expression	=AND(M2>0,T2=0)	text	YES
B2:B12	Expression	=AND(M2=0,T2>0)	text	YES
B2:B12	Expression	=AND(M2>0,T2>0)	text	YES

BillTony · Sep 18, 2023

Thanks so much!
This is a great solution and works QUITE well!
Would love to see it in a VB format...

Brunts · Sep 19, 2023

Moving the Columns
Cell Value
A1 CUST_1
B1 CUST_2
C1 CUST_3
D1 Result

Clunky macros code below (Just doing the same thing as the excel formulas):-

VBA Code:

Option Explicit

Sub Matching_Values()
'*************************************************************************************************************
'* Are values in the CUST_1 Column contained in the CUST_2 and CUST_3 columns. Highlight columns and insert
'* values in the Results column confirming whether there are matches (FAIL) or not (PASS)
'* - Cells will be highlighted as Yellow when values are contained in the CUST_2 column/Row and also in the
'*   CUST_1 column
'* - Cells will be highlighted as Orange when values are contained in the CUST_3 column/Row and also in the
'*   CUST_1 column.
'* - Cells will be highlighted as Red when values are contained in CUST_1, CUST_2 and CUST_3 columns/Row
'*************************************************************************************************************
    Dim LastRow As Long, Result_Column As Long
    Dim Cond_Form As Range
    
    
'*************************************************************************************************************
'* STEP 1 - Confirm location of the Results column in Row 1 and the last occupied row.
'*        - Delete the columns after the Results column
'*************************************************************************************************************
    Range("A1").Select
    LastRow = 100
    Result_Column = 0
  
    With Sheets("Sheet1")
        With .Rows(1)
            On Error Resume Next
            Result_Column = .Find("Result", .Cells(.Cells.Count), xlValues, , xlByColumns, xlPrevious).Column
            On Error GoTo 0
        End With
    End With
    
    If Result_Column > 0 Then
        Columns(Result_Column + 1).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Application.CutCopyMode = False
        Selection.Delete Shift:=xlToLeft
        Range("A1").Select
    Else
        MsgBox "There is no Column called 'Result' in Row 1 - macro stopped"
        GoTo Error_End:
    End If
    
'*  THE LAST ROW OF THE USED RANGE
    Range("A1").Select
    ActiveSheet.UsedRange
    LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row

    If LastRow <= 1 Then
        MsgBox "There is no data in the columns - macro stopped"
        GoTo Error_End:
    End If



'*************************************************************************************************************
'* STEP 2 - Remove the Conditional Formatting from the CUST_1, CUST_2 and CUST_3 Columns
'*************************************************************************************************************
    Range(Cells(2, 1), Cells(LastRow, 3)).Select
    Selection.FormatConditions.Delete
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With


        
'*************************************************************************************************************
'* STEP 3 - Inserting the headings and formula for calculating whether there are matching values
'*************************************************************************************************************
    Cells(1, Result_Column + 1).Value = "No of values in CUST_2 Column"
    Cells(1, Result_Column + 2).Value = "Is first value in Cust_2 column in the Cust_1 Column"
    Cells(1, Result_Column + 3).Value = "Is Second value in Cust_2 column in the Cust_1 Column"
    Cells(1, Result_Column + 4).Value = "Is Third value in Cust_2 column in the Cust_1 Column"
    Cells(1, Result_Column + 5).Value = "Is Fourth value in Cust_2 column in the Cust_1 Column"
    Cells(1, Result_Column + 6).Value = "Is Fifth value in Cust_2 column in the Cust_1 Column"
    Cells(1, Result_Column + 7).Value = "Count of Cust_2 column matches"
    Cells(1, Result_Column + 8).Value = "No of values in CUST_3 Column"
    Cells(1, Result_Column + 9).Value = "Is first value in Cust_3 column in the Cust_1 Column"
    Cells(1, Result_Column + 10).Value = "Is Second value in Cust_3 column in the Cust_1 Column"
    Cells(1, Result_Column + 11).Value = "Is Third value in Cust_3 column in the Cust_1 Column"
    Cells(1, Result_Column + 12).Value = "Is Fourth value in Cust_3 column in the Cust_1 Column"
    Cells(1, Result_Column + 13).Value = "Is Fifth value in Cust_3 column in the Cust_1 Column"
    Cells(1, Result_Column + 14).Value = "Count of Cust_3 column matches"
        
    Cells(2, Result_Column + 1).FormulaR1C1 = "=LEN(RC[-3])-LEN(SUBSTITUTE(RC[-3],"","",""""))+1"
    Cells(2, Result_Column + 2).FormulaR1C1 = "=IFERROR(IF(FIND(IFERROR(LEFT(RC[-4],(FIND("","",RC[-4],1)-1)),RC[-4]),RC1)>0,""MATCH"",""OK""),""OK"")"
    Cells(2, Result_Column + 3).FormulaR1C1 = "=IF(RC[-2]>=2,(IFERROR(IF(FIND(TRIM(MID(SUBSTITUTE(RC[-5],"","",REPT("" "",100)),100,100)),RC[-6])>0,""MATCH"",""OK""),""OK"")),"""")"
    Cells(2, Result_Column + 4).FormulaR1C1 = "=IF(RC[-3]>=3,(IFERROR(IF(FIND(TRIM(MID(SUBSTITUTE(RC[-6],"","",REPT("" "",100)),200,100)),RC[-7])>0,""MATCH"",""OK""),""OK"")),"""")"
    Cells(2, Result_Column + 5).FormulaR1C1 = "=IF(RC[-4]>=4,(IFERROR(IF(FIND(TRIM(MID(SUBSTITUTE(RC[-7],"","",REPT("" "",100)),300,100)),RC[-8])>0,""MATCH"",""OK""),""OK"")),"""")"
    Cells(2, Result_Column + 6).FormulaR1C1 = "=IF(RC[-5]>=5,(IFERROR(IF(FIND(TRIM(MID(SUBSTITUTE(RC[-8],"","",REPT("" "",100)),400,100)),RC[-9])>0,""MATCH"",""OK""),""OK"")),"""")"
    Cells(2, Result_Column + 7).FormulaR1C1 = "=COUNTIF(RC[-5]:RC[-1],""MATCH"")"
    Cells(2, Result_Column + 8).FormulaR1C1 = "=LEN(RC[-9])-LEN(SUBSTITUTE(RC[-9],"","",""""))+1"
    Cells(2, Result_Column + 9).FormulaR1C1 = "=IFERROR(IF(FIND(IFERROR(LEFT(RC[-10],(FIND("","",RC[-10],1)-1)),RC[-10]),RC1)>0,""MATCH"",""OK""),""OK"")"
    Cells(2, Result_Column + 10).FormulaR1C1 = "=IF(RC[-2]>=2,(IFERROR(IF(FIND(TRIM(MID(SUBSTITUTE(RC[-11],"","",REPT("" "",100)),100,100)),RC[-13])>0,""MATCH"",""OK""),""OK"")),"""")"
    Cells(2, Result_Column + 11).FormulaR1C1 = "=IF(RC[-3]>=3,(IFERROR(IF(FIND(TRIM(MID(SUBSTITUTE(RC[-12],"","",REPT("" "",100)),200,100)),RC[-14])>0,""MATCH"",""OK""),""OK"")),"""")"
    Cells(2, Result_Column + 12).FormulaR1C1 = "=IF(RC[-4]>=4,(IFERROR(IF(FIND(TRIM(MID(SUBSTITUTE(RC[-13],"","",REPT("" "",100)),300,100)),RC[-15])>0,""MATCH"",""OK""),""OK"")),"""")"
    Cells(2, Result_Column + 13).FormulaR1C1 = "=IF(RC[-5]>=5,(IFERROR(IF(FIND(TRIM(MID(SUBSTITUTE(RC[-14],"","",REPT("" "",100)),400,100)),RC[-16])>0,""MATCH"",""OK""),""OK"")),"""")"
    Cells(2, Result_Column + 14).FormulaR1C1 = "=COUNTIF(RC[-5]:RC[-1],""MATCH"")"
    
    Range(Cells(1, Result_Column + 1), Cells(1, Result_Column + 14)).Font.Bold = True
    Cells.Select
    Cells.EntireColumn.AutoFit
    
    Range(Cells(2, Result_Column + 1), Cells(2, Result_Column + 14)).Select
    Selection.AutoFill Destination:=Range(Cells(2, Result_Column + 1), Cells(LastRow, Result_Column + 14)), Type:=xlFillDefault
    Range("A1").Select
    
    
    
'*************************************************************************************************************
'* STEP 4 - Inserting the Formula into the Results column. Copying down the rows and then removing formula
'*          and making them values
'*************************************************************************************************************
    Cells(2, Result_Column).FormulaR1C1 = "=IF(OR(RC[7]>0,RC[14]>0),""FAIL"",""PASS"")"
    Range(Cells(2, Result_Column), Cells(2, Result_Column)).Select
    Selection.AutoFill Destination:=Range(Cells(2, Result_Column), Cells(LastRow, Result_Column)), Type:=xlFillDefault
    Range(Cells(2, Result_Column), Cells(LastRow, Result_Column)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
    
        
        
'*************************************************************************************************************
'* STEP 5a - Applying the Conditional formatting from Range A2:C2 to the no of rows in the range - A2
'*************************************************************************************************************
   Range("A2").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(K2>0,R2=0)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = True
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(K2=0,R2>0)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = True
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(K2>0,R2>0)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = True
    
    
'*************************************************************************************************************
'* STEP 5b - Applying the Conditional formatting from Range B2:D2 to the no of rows in the range - B2
'*************************************************************************************************************
    Range("B2").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=K2>0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = True
    
    
'*************************************************************************************************************
'* STEP 5c - Applying the Conditional formatting from Range B2:D2 to the no of rows in the range - C2
'*************************************************************************************************************
    Range("C2").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=R2>0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = True
    
    
'*************************************************************************************************************
'* STEP 5d - Copying the conditional formatting down the range.
'*************************************************************************************************************
    Range(Cells(2, 1), Cells(2, 3)).Select
    Selection.Copy
    Range(Cells(2, 1), Cells(LastRow, 3)).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    
    
'*************************************************************************************************************
'* STEP 5e - Remove the conditional formatting but retain the colours
'*************************************************************************************************************
    For Each Cond_Form In Selection
        Cond_Form.Interior.Color = Cond_Form.DisplayFormat.Interior.Color
    Next

    Selection.FormatConditions.Delete
    Result_Column = Result_Column


        
'*************************************************************************************************************
'* STEP 6 - Confirm location of the Results column in Row 1 and the last occupied row.
'*        - Delete the columns after the Results + 1 column
'*************************************************************************************************************
    Range("A1").Select
    LastRow = 100
    Result_Column = 0
  
    With Sheets("Sheet1")
        With .Rows(1)
            On Error Resume Next
            Result_Column = .Find("Result", .Cells(.Cells.Count), xlValues, , xlByColumns, xlPrevious).Column
            On Error GoTo 0
        End With
    End With
    
    If Result_Column > 0 Then
        Columns(Result_Column + 1).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Application.CutCopyMode = False
        Selection.Delete Shift:=xlToLeft
        Range("A1").Select
    End If
 
Error_End:

End Sub

BillTony · Sep 20, 2023

Great stuff!

Thanks again for ALL your help with this!

dreid1011 · Sep 20, 2023

I know you already have a working solution, I just saw this and took it as a challenge to my ability to see if I could make it in a single formula with no helper columns and I'm sure it can be refined even further, but this is what I came up with:

Book1

A

B

C

D

1

CUST_1

CUST_2

CUST_3

RESULT

2

Val 1

Val 2, Val 1

Val 2, Val 3

Error

3

Val 2

Val 3

Val 2

Error

4

Val 3

Val 3, Val 1

Val 5

Error

5

Val 4

Val 1

Val 4

Error

6

Val 5

Val 2, Val 1

Val 3, Val 4

7

Val 1, Val 2

Val 4

Error

8

Val 1, Val 2, Val 3

Val 2, Val 1

Val 5

Error

9

Val 1, Val 2, Val 3, Val 4

Val 5

Val 1

Error

10

Val 1, Val 2, Val 3, Val 4, Val 5

Val 1, Val 4

Val 3, Val 4, Val 5

Error

11

Val 3, Val 5

Val 1

Val 4

12

Val 1, Val 2, Val 3

Val 3

Val 1

Error

Sheet2

Cell Formulas
Range	Formula
D2:D12	D2	=IF(SUM(IFERROR(SEARCH(TRIM(TEXTSPLIT(A2,",")),B2),0))+SUM(IFERROR(SEARCH(TRIM(TEXTSPLIT(A2,",")),C2),0)),"Error","")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A12	Expression	=SUM(IFERROR(SEARCH(TRIM(TEXTSPLIT(A2,",")),B2),0))+SUM(IFERROR(SEARCH(TRIM(TEXTSPLIT(A2,",")),C2),0))>0	text	NO
B2:B12	Expression	=SUM(IFERROR(SEARCH(TRIM(TEXTSPLIT(A2,",")),B2),0))>0	text	NO
C2:C12	Expression	=SUM(IFERROR(SEARCH(TRIM(TEXTSPLIT(A2,",")),C2),0))	text	NO
D2:D12	Expression	=SUM(IFERROR(SEARCH(TRIM(TEXTSPLIT(A2,",")),B2),0))+SUM(IFERROR(SEARCH(TRIM(TEXTSPLIT(A2,",")),C2),0))	text	NO

Evaluate content based on multiple criteria.

BillTony

Board Regular

Excel Facts

Brunts

Board Regular

BillTony

Board Regular

Brunts

Board Regular

BillTony

Board Regular

Brunts

Board Regular

BillTony

Board Regular

Brunts

Board Regular

BillTony

Board Regular

dreid1011

Well-known Member

Similar threads

Forum statistics

Share this page

Evaluate content based on multiple criteria.

Board Regular

Excel Facts

Board Regular

Board Regular

Board Regular

Board Regular

Board Regular

Board Regular

Board Regular

Board Regular

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock