lookup by comma delimited list with single value list

[cerchiarax]

Hi guys,

I've been trying to get a solution my self and I searched the thread and found the following below that partially got what I was after.

https://www.mrexcel.com/forum/excel-questions/546013-find-value-cell-comma-delimited-list.html


https://www.mrexcel.com/forum/excel...-separated-list-pull-cell-value-same-row.html

Below I have 2 tables, I'm looking to match the table 1 "ID" that is comma delimited with the single "ID" that is in Table 2. So that I could get all the "KEY'S" that are in Table 1 that match with these ID's to look like the "RESULT" Column that I manually created in table 2.

Table 1
[TABLE="width: 200"]
<tbody>[TR]
[TD]ID[/TD]
[TD]KEY[/TD]
[/TR]
[TR]
[TD][TABLE="width: 246"]
<tbody>[TR]
[TD="class: xl65, width: 246"]240642,12345,82871[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 237"]
<tbody>[TR]
[TD="width: 237"]RT12347789[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 246"]
<tbody>[TR]
[TD="class: xl65, width: 246"]24312,12345,82872[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 237"]
<tbody>[TR]
[TD="width: 237"]RT12347790[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 246"]
<tbody>[TR]
[TD="class: xl65, width: 246"]24568,12345,82873[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 237"]
<tbody>[TR]
[TD="width: 237"]RT12347789[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 246"]
<tbody>[TR]
[TD="class: xl65, width: 246"]24568,12345,82873[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 237"]
<tbody>[TR]
[TD="width: 237"]RT12347792[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Table 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]RESULT[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]RT12347789,RT12347790,RT12347792[/TD]
[/TR]
[TR]
[TD]240642[/TD]
[TD]RT12347789[/TD]
[/TR]
[TR]
[TD]24568[/TD]
[TD]RT12347789, RT12347792[/TD]
[/TR]
[TR]
[TD]24312[/TD]
[TD]RT12347790[/TD]
[/TR]
</tbody>[/TABLE]


Thanks ahead of time with any help you guys can provide.

 

[sandy666]

is that what you want?

with PowerQuery (aka Get&Transform)

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"KEY", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"ID", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "ID"),
    #"Grouped Rows" = Table.Group(#"Split Column by Delimiter", {"ID"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Result", each Table.Column([Count],"KEY")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Result", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Sorted Rows" = Table.Sort(#"Extracted Values",{{"ID", Order.Ascending}})
in
    #"Sorted Rows"[/SIZE]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]ID[/td][td=bgcolor:#5B9BD5]KEY[/td][td][/td][td=bgcolor:#70AD47]ID[/td][td=bgcolor:#70AD47]Result[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]240642,12345,82871[/td][td=bgcolor:#DDEBF7]RT12347789[/td][td][/td][td=bgcolor:#E2EFDA]12345[/td][td=bgcolor:#E2EFDA]RT12347789,RT12347790,RT12347789,RT12347792[/td][/tr]

[tr=bgcolor:#FFFFFF][td]24312,12345,82872[/td][td]RT12347790[/td][td][/td][td]240642[/td][td]RT12347789[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]24568,12345,82873[/td][td=bgcolor:#DDEBF7]RT12347789[/td][td][/td][td=bgcolor:#E2EFDA]24312[/td][td=bgcolor:#E2EFDA]RT12347790[/td][/tr]

[tr=bgcolor:#FFFFFF][td]24568,12345,82873[/td][td]RT12347792[/td][td][/td][td]24568[/td][td]RT12347789,RT12347792[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]82871[/td][td=bgcolor:#E2EFDA]RT12347789[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]82872[/td][td]RT12347790[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]82873[/td][td=bgcolor:#E2EFDA]RT12347789,RT12347792[/td][/tr]
[/table]

 

[cerchiarax]

Let me give it a try. I have never used Powerquery before. But I do see that it is a free addin.

 

[sandy666]

Free add-in for 2010/2013
Built-in for 2016 and higher

 

[cerchiarax]

Unfortunately I'm on 2013 at work and waiting to get approved for powerquery addin. Hopefully it will be soon as it sounds like it would be useful to have. I was wondering if there is any other way we can do this with a mix of a index match formula and a text join or maybe a Macro.

 

[sandy666]

PQ is very useful.
TEXTJOIN() is from 365 so it doesn't exist in 2013.

Maybe someone else will give you formula or VBA solution

Good luck and have a nice day

 

[Fluff]

How about

Function MyLookup(St As String, Rng As Range) As String
   Dim cl As Range
   For Each cl In Rng
      If InStr(1, cl, St) > 0 Then
         If MyLookup = "" Then MyLookup = cl.Offset(, 1) Else MyLookup = MyLookup & ", " & cl.Offset(, 1)
      End If
   Next cl
End Function

Excel 2013/2016
	A	B	C	G	H	I	J
1	ID	KEY				ID
2	240642, 12345, 82871	RT12347789				12345	RT12347789, RT12347790, RT12347789, RT12347792
3	24312,12345, 82872	RT12347790				240642	RT12347789
4	24568, 12345, 82873	RT12347789				24568	RT12347789, RT12347792
5	24568, 12345, 82873	RT12347792				24312	RT12347790
vor

Cell Formulas
Range	Formula
J2	=MyLookup(I2,A$2:A$5)

 

[cerchiarax]

Hi Fluff, I think this is working, I do get some "#VALUE!" errors not sure why yet. The way I'm doing this is by comparing 2 worksheets "=MYLOOKUP(A2,RESULTS!$A$2:$A$14075)". Also is there a way to have the result in the J column only show the unique ID's and non duplicates. Like in column 2J could it be just "RT12347789", "RT12347790", "RT12347792"

 

[Fluff]

Try

Function MyLookup(St As String, Rng As Range) As String
   Dim Cl As Range
   With CreateObject("scripting.dictionary")
      For Each Cl In Rng
         If InStr(1, Cl, St) > 0 Then .Item(Cl.Offset(, 1).Value) = Empty
      Next Cl
      MyLookup = Join(.keys, ", ")
   End With
End Function

 

[cerchiarax]

I think the #VALUE errors is happening because of the duplicates as I only get the error for 2 ID's where the KEY has a dupe of over 2K records. Just seeing the new code. Will try that now. Thanks!