Cell with comma seperated values and vlookup

[chrisnilu]

1st table/report has cells with single values as well as multiple values separated by comma.

[TABLE="class: outer_border, width: 60"]
<tbody>[TR]
[TD]Identifier
[/TD]
[/TR]
[TR]
[TD]AA
[/TD]
[/TR]
[TR]
[TD]XY,AA,YX
[/TD]
[/TR]
[TR]
[TD]BE
[/TD]
[/TR]
[TR]
[TD]RE,WX,EW,XT
[/TD]
[/TR]
[TR]
[TD]AB,ER,
[/TD]
[/TR]
</tbody>[/TABLE]


if any of the values in the cell is there in the 2nd table I need to filter the 1st report.

2nd table/report
[TABLE="class: outer_border, width: 54"]
<tbody>[TR]
[TD]Identifier
[/TD]
[/TR]
[TR]
[TD]WR
[/TD]
[/TR]
[TR]
[TD]AA
[/TD]
[/TR]
[TR]
[TD]XY
[/TD]
[/TR]
[TR]
[TD]BE
[/TD]
[/TR]
</tbody>[/TABLE]

As BE & AA are available in the 2nd table, 1st report should be filtered and should show only the 1st 3 rows (table values with BE & AA )

If the 1st report/table only has single values in a cell I can do a vlookup but as this has comma separated values how should I approach this ?

seeking for immediate assistance as I would need this for my work tomorrow. THANKS

 

[chrisnilu]

Maybe Advanced Filter + a formula

This was very helpful and look simple but in my case I have to do the filtering in Column A if the same value is there in Column C ( cannot do it the other way due to the nature of data ) . Column A cells might have single or multiple values and Column C will always have single values.

 

[chrisnilu]

its 7 character

 

[chrisnilu]

I have no idea how fast this will be given the number of rows involved, but possibly this macro (the layout info is set in the four highlighted Const statements)...

Getting Run time error '1004' AutoFilter method of Range Class failed. As Im new to Macro don't know how to correct this,appreciate your assistance.

 

[Rick Rothstein]

Getting Run time error '1004' AutoFilter method of Range Class failed. As Im new to Macro don't know how to correct this,appreciate your assistance.

Did you follow my instructions and change the red highlighted lines of code to match your actual setup? You should be able to figure out what should be assigned to what based on the names I gave for the constants (that is what Const statements do... define constants to be used in the rest of the code).

 

[Marcelo Branco]

Another macro - not tested with huge data, but i think it's fast

Sub aTest()
    Dim dic As Object, vData As Variant, vSearch As Variant
    Dim i As Long, j As Long, spl As Variant, strRows As String
    Dim bFound As Boolean
   
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = vbTextCompare
    
    vSearch = Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row)
    For i = LBound(vSearch) To UBound(vSearch)
            dic(vSearch(i, 1)) = Empty
    Next i
    
    vData = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    For i = LBound(vData) To UBound(vData)
        bFound = False
        spl = Split(Replace(vData(i, 1), " ", ""), ",")
        For j = LBound(spl) To UBound(spl)
            If dic.exists(spl(j)) Then
                bFound = True
                Exit For
            End If
        Next j
        If Not bFound Then
            If Len(strRows & "," & i + 1 & ":" & i + 1) < 255 Then
                strRows = strRows & "," & i + 1 & ":" & i + 1
            Else
                 Range(Mid(strRows, 2)).EntireRow.Hidden = True
                 Rows(i + 1).EntireRow.Hidden = True
                 strRows = ""
            End If
        End If
    Next i
    If Len(strRows) Then Range(Mid(strRows, 2)).EntireRow.Hidden = True
End Sub

M.