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

ID	KEY
240642,12345,82871 <tbody> </tbody>	RT12347789 <tbody> </tbody>
24312,12345,82872 <tbody> </tbody>	RT12347790 <tbody> </tbody>
24568,12345,82873 <tbody> </tbody>	RT12347789 <tbody> </tbody>
24568,12345,82873 <tbody> </tbody>	RT12347792 <tbody> </tbody>

<tbody>
</tbody>

Table 2

ID	RESULT
12345	RT12347789,RT12347790,RT12347792
240642	RT12347789
24568	RT12347789, RT12347792
24312	RT12347790

<tbody>
</tbody>

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]

ID	KEY		ID	Result
240642,12345,82871	RT12347789		12345	RT12347789,RT12347790,RT12347789,RT12347792
24312,12345,82872	RT12347790		240642	RT12347789
24568,12345,82873	RT12347789		24312	RT12347790
24568,12345,82873	RT12347792		24568	RT12347789,RT12347792
			82871	RT12347789
			82872	RT12347790
			82873	RT12347789,RT12347792

 

[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!