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

 

[pbornemeier]

Use the autofilter.
Unselect all
Filter for each item on the second table by typing them in one at a time
The first time any rows are returned accept that filter.
The second and subsequent times rows are returned, choose the option "Add current selection to filter each time"
The lines from table 1 that contain any of the items entered will be displayed.

 

[FormR]

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 ?

Hi, another option you could try:

Excel 2013/2016
	A	B	C	D
1	Identifier	Filter ?		Table 2
2	AA	TRUE		WR
3	XY,AA,YX	TRUE		AA
4	BE	TRUE		XY
5	RE,WX,EW,XT	FALSE		BE
6	AB,ER,	FALSE
Sheet1

Cell Formulas
Range	Formula
B2	=ISNUMBER(LOOKUP(1,-SEARCH(","&$D$2:$D$5&",",","&A2&",")))

 

[chrisnilu]

Unfortunately both the tables has thousands of records

 

[Rick Rothstein]

Unfortunately both the tables has thousands of records

What is the maximum number of rows for Table 1 (don't really need to know the number for the second table for the method I am thinking about)?

Are your tables real Excel Table objects or just groups of cells that you think of as tables?

Where are the tables located at?

 

[Marcelo Branco]

Maybe Advanced Filter + a formula

Something like

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Identifier​

[/td][td][/td][td]

Identifier​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

AA​

[/td][td][/td][td]

WR​

[/td][td][/td][td]

TRUE​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

XY,AA,YX​

[/td][td][/td][td]

AA​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

BE​

[/td][td][/td][td]

XY​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

RE,WX,EW,XT​

[/td][td][/td][td]

BE​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

AB,ER,​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Leave E1 blank

Formula in E2
=SUMPRODUCT(--ISNUMBER(SEARCH(C$2:C$5,A2)))>0

Select A1:A6 and apply Advanced Filter with Criteria Range=$E$1:$E$2

Hope this helps

M.

 

[Peter_SSs]

Maybe Advanced Filter + a formula

Hi Marcelo
I also considered Adv Filter but the OP says thousands of rows in both tables and, for me at least, that made Adv Filter painfully slow.

Also, just noting with your formula that the OP would need to confirm that all individual Identifiers are exactly 2 characters long. Otherwise a formula like FormR's would be required.

 

[Marcelo Branco]

Hi Peter,

You are right, with thousands of rows in both tables adv filter should not be used - bad performance.
Maybe a macro using variant arrays...

M.

 

[Rick Rothstein]

Maybe a macro using variant arrays...

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)...

[table="width: 500"]
[tr]
	[td]Sub FilterIndentifiers()
  Dim R As Long, UnusedCol As Long, Table2 As Variant
  
[B][COLOR="#FF0000"]  Const Table1SheetName = "Sheet1"
  Const Table2SheetName = "Sheet2"
  Const Table1Col = "A"
  Const Table2Col = "A"
[/COLOR][/B]  
  With Sheets(Table2SheetName)
    Table2 = .Range(.Cells(2, Table2Col), .Cells(Rows.Count, Table2Col).End(xlUp))
  End With
  Application.ScreenUpdating = False
  With Sheets(Table1SheetName)
    UnusedCol = .Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column + 1
    .AutoFilterMode = False
    .Rows.Hidden = False
    With .Range(.Cells(1, Table1Col), .Cells(Rows.Count, Table1Col).End(xlUp))
      For R = 1 To UBound(Table2)
        .AutoFilter 1, "*" & Table2(R, 1) & "*"
        Intersect(.SpecialCells(xlVisible).EntireRow, .Columns(UnusedCol)).Value = "X"
      Next
      .Parent.AutoFilterMode = False
      .Columns(UnusedCol).SpecialCells(xlBlanks).EntireRow.Hidden = True
      .Columns(UnusedCol).Clear
    End With
  End With
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

 

[chrisnilu]

this looks 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.