Match comma delimited values in a cell against individual values in a column

[PeteSmith]

Hi,

I would like to compare a list of values in column B against a list of values on every row in column M contained within a single comma delimited cell. There are somewhere in the region of 3000 rows so i want to avoid manually transposing a row at a time to find duplicates.

What i'm looking to do is match each of the comma delimited values in column M against the individual values on every row in column B.

For every row where all values in Column M are matched against values in column B i want a to return a TRUE value in column O. If any values are not found in column B i want to return a FALSE value in column O.

[TABLE="width: 500"]
<TBODY>[TR]
[TD]Colum B[/TD]
[TD]Column M[/TD]
[TD]Column O[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]Peter, David, Claire[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Paul, Peter, David, Claire, Thomas[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]Thomas[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]Thomas[/TD]
[TD]Thomas, Claire, Peter[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]Claire[/TD]
[TD]Peter[/TD]
[TD]FALSE[/TD]
[/TR]
</TBODY>[/TABLE]


Any help very much appreciated.

 

[mirabeau]

Hi,

I would like to compare a list of values in column B against a list of values on every row in column M contained within a single comma delimited cell. There are somewhere in the region of 3000 rows so i want to avoid manually transposing a row at a time to find duplicates.

What i'm looking to do is match each of the comma delimited values in column M against the individual values on every row in column B.

For every row where all values in Column M are matched against values in column B i want a to return a TRUE value in column O. If any values are not found in column B i want to return a FALSE value in column O.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Colum B[/TD]
[TD]Column M[/TD]
[TD]Column O[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]Peter, David, Claire[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Paul, Peter, David, Claire, Thomas[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]Thomas[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]Thomas[/TD]
[TD]Thomas, Claire, Peter[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]Claire[/TD]
[TD]Peter[/TD]
[TD]FALSE[/TD]
[/TR]
</tbody>[/TABLE]


Any help very much appreciated.

Could you be clearer about your rules for determining true or false? Maybe I'm missing something but I can't pick up on your logic.

Are you suggesting for instance that in order to have TRUE in columnO and there are 3000 rows, then you must have 3000 matching comma separated values in columnM??

 

[PeteSmith]

The previous table i posted was completely wrong and didn't match the description. Apologies, here is a better representation of my data.

[TABLE="width: 500"]
<TBODY>[TR]
[TD]Column B[/TD]
[TD]Column M[/TD]
[TD]Column O[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]Peter, David, Paul, Sarah[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Paul, Peter, David, Claire, Thomas[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]Thomas[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]Thomas[/TD]
[TD]Thomas, Claire, Peter[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]Claire[/TD]
[TD]Peter[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]Mandy[/TD]
[TD]Suzan, Thomas, Paul[/TD]
[TD]FALSE[/TD]
[/TR]
</TBODY>[/TABLE]

Column O is false on rows 2 & 7 as neither Sarah or Suzan appear anywhere in column B but do appear in column M in the comma delimited cell.

 

[mirabeau]

The previous table i posted was completely wrong and didn't match the description. Apologies, here is a better representation of my data.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column B[/TD]
[TD]Column M[/TD]
[TD]Column O[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]Peter, David, Paul, Sarah[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Paul, Peter, David, Claire, Thomas[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]Thomas[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]Thomas[/TD]
[TD]Thomas, Claire, Peter[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]Claire[/TD]
[TD]Peter[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]Mandy[/TD]
[TD]Suzan, Thomas, Paul[/TD]
[TD]FALSE[/TD]
[/TR]
</tbody>[/TABLE]

Column O is false on rows 2 & 7 as neither Sarah or Suzan appear anywhere in column B but do appear in column M in the comma delimited cell.

Hi Pete,

If you'd like to try a macro, then will this do it?

Sub matchups()
Dim d As Object, c, b() As Boolean
Dim k As Long, q, flg As Byte
Set d = CreateObject("scripting.dictionary")
For Each c In Range("B1", Cells(Rows.Count, "B").End(3)).Value
    d(c) = 1
Next c
For Each c In Range("M2", Cells(Rows.Count, "M").End(3)).Value
    k = k + 1: flg = 0
    ReDim Preserve b(1 To k)
    For Each q In Split(c, ",")
        If d(Trim(q)) <> 1 Then flg = 1: Exit For
    Next q
    If flg = 0 Then b(k) = 1
Next c
Range("O2").Resize(k) = Application.Transpose(b)
End Sub

Did you want your names in bold marked that way by the macro as well?

 

[PeteSmith]

That's fantastic, works a treat. And yes if it's easy enough to mark the names in bold that would be the icing on the cake!

 

[Firefly2012]

Hi Pete

If you wanted to consider a formula option, here's one that should work (it definitely won't bold the missing name, however!):

Excel 2010
	B	M	N	O	P
1	Column B	Column M		Column O
2	Peter	Peter, David, Paul,Sarah		FALSE	FALSE
3	David	Paul, Peter, David, Claire, Thomas		TRUE	TRUE
4	Paul	Thomas		TRUE	TRUE
5	Thomas	Thomas, Claire, Peter		TRUE	TRUE
6	Claire	Peter		TRUE	TRUE
7	Mandy	Suzan, Thomas, Paul		FALSE	FALSE
Sheet1

Cell Formulas
Range	Formula
P2	{=COUNT(MATCH(TRIM(MID(SUBSTITUTE(M2,",",REPT(" ",100)),(Num_Array-1)*100+1,100)),$B$2:$B$7,0))=(LEN(M2)-LEN(SUBSTITUTE(M2,",",""))+1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
Num_Array	=ROW(INDIRECT("1:255"))

 

[mirabeau]

That's fantastic, works a treat. And yes if it's easy enough to mark the names in bold that would be the icing on the cake!

Thanx for the feedback. Good that it worked OK.

To bold the first of the "false" strings in a columnM cell, try this

Sub matchups2()
Dim d As Object, c, b() As Boolean
Dim k As Long, q, flg As Byte, s As Range
Set d = CreateObject("scripting.dictionary")
For Each c In Range("B1", Cells(Rows.Count, "B").End(3)).Value
    d(c) = 1
Next c
For Each c In Range("M2", Cells(Rows.Count, "M").End(3)).Value
    k = k + 1: flg = 0
    ReDim Preserve b(1 To k)
    For Each q In Split(c, ",")
        If d(Trim(q)) <> 1 Then
            flg = 1
            Set s = Cells(k + 1, "m")
            s.Characters(InStr(s, q), Len(q)).Font.Bold = True
            Exit For
        End If
    Next q
    If flg = 0 Then b(k) = 1
Next c
Range("O2").Resize(k) = Application.Transpose(b)
End Sub