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
 

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,709
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,212
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,498
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,317
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
42,169
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,317
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,498
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.
 

Forum statistics

Threads
1,081,545
Messages
5,359,450
Members
400,528
Latest member
Ratish52

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top