Cell with comma seperated values and vlookup

chrisnilu

New Member
Joined
Aug 8, 2017
Messages
8
1st table/report has cells with single values as well as multiple values separated by comma.

Identifier
AA
XY,AA,YX
BE
RE,WX,EW,XT
AB,ER,

<tbody>
</tbody>


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

2nd table/report
Identifier
WR
AA
XY
BE

<tbody>
</tbody>

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
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,795
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.
 
Last edited:

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,382
Office Version
365
Platform
Windows
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:

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Identifier</td><td style=";">Filter ?</td><td style="text-align: right;;"></td><td style=";">Table 2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">AA</td><td style="text-align: right;background-color: #FFFF00;;">TRUE</td><td style="text-align: right;;"></td><td style=";">WR</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">XY,AA,YX</td><td style="text-align: right;;">TRUE</td><td style="text-align: right;;"></td><td style=";">AA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">BE</td><td style="text-align: right;;">TRUE</td><td style="text-align: right;;"></td><td style=";">XY</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">RE,WX,EW,XT</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;"></td><td style=";">BE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">AB,ER,</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=ISNUMBER(<font color="Blue">LOOKUP(<font color="Red">1,-SEARCH(<font color="Green">","&$D$2:$D$5&",",","&A2&","</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,817
Office Version
2010
Platform
Windows
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
Maybe Advanced Filter + a formula

Something like

A
B
C
D
E
1
Identifier​
Identifier​
2
AA​
WR​
TRUE​
3
XY,AA,YX​
AA​
4
BE​
XY​
5
RE,WX,EW,XT​
BE​
6
AB,ER,​
7

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,419
Office Version
365
Platform
Windows
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,817
Office Version
2010
Platform
Windows
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)...
Code:
[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]
 
Last edited:

chrisnilu

New Member
Joined
Aug 8, 2017
Messages
8
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,592
Messages
5,445,381
Members
405,328
Latest member
ripmurdock

This Week's Hot Topics

Top