excelisfun array multiple lookup in one cell

[dmj120]

I came across an excelisfun video a while ago in which a lookup was used that showed all values in one cell. VERY similar to the textjoin(), but it worked in 2010.

....and of course, I can't find it, nor do I remember what I searched when I stumbled upon it....

I have a two column table, column A has names, column B has PO#s. I want to have a summary in which all the PO#s display in one cell.

Example:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Selection
[/TD]
[TD]PO Numbers
[/TD]
[/TR]
[TR]
[TD]Josh
[/TD]
[TD]100, 150, 200
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]PO Number
[/TD]
[/TR]
[TR]
[TD]Josh
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]Jim
[/TD]
[TD]99999
[/TD]
[/TR]
[TR]
[TD]Josh
[/TD]
[TD]150
[/TD]
[/TR]
[TR]
[TD]Heather
[/TD]
[TD]88888
[/TD]
[/TR]
[TR]
[TD]Josh
[/TD]
[TD]200
[/TD]
[/TR]
</tbody>[/TABLE]


Does anyone know what I can search to find the video, or give me a starting point??

Thanks!

 

[sandy666]

you can try with PowerQuery (free add-in for 2010)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Name[/td][td=bgcolor:#5B9BD5]PO Number[/td][td][/td][td=bgcolor:#70AD47]Name[/td][td=bgcolor:#70AD47]PO Number[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Josh[/td][td=bgcolor:#DDEBF7]

100​

[/td][td][/td][td=bgcolor:#E2EFDA]Josh[/td][td=bgcolor:#E2EFDA]100, 150, 200[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Jim[/td][td]

99999​

[/td][td][/td][td]Jim[/td][td]99999[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Josh[/td][td=bgcolor:#DDEBF7]

150​

[/td][td][/td][td=bgcolor:#E2EFDA]Heather[/td][td=bgcolor:#E2EFDA]88888[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Heather[/td][td]

88888​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Josh[/td][td=bgcolor:#DDEBF7]

200​

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

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Name"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "PO Number", each Table.Column([Count],"PO Number")),
    Extract = Table.TransformColumns(List, {"PO Number", each Text.Combine(List.Transform(_, Text.From), ", "), type text})
in
    Extract[/SIZE]

 

[Marcelo Branco]

A possible solution without TEXTJOIN or Macro


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

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

B​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Selection​

[/TD]
[TD]

PO Numbers​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Josh​

[/TD]
[TD]

100, 150, 200​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3
​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4
​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5
​

[/TD]
[TD]

Name​

[/TD]
[TD]

PO Number​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6
​

[/TD]
[TD]

Josh​

[/TD]
[TD]

100​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7
​

[/TD]
[TD]

Jim​

[/TD]
[TD]

99999​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8
​

[/TD]
[TD]

Josh​

[/TD]
[TD]

150​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

Heather​

[/TD]
[TD]

88888​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

Josh​

[/TD]
[TD]

200​

[/TD]
[/TR]
</tbody>[/TABLE]


Select B2

Type in the Formula Bar this formula
=SUBSTITUTE(TRIM(CONCATENATE(IF(A6:A10=A2,B6:B10&" ","")))," ",", ")

With the mouse select the IF part (in red)

Hit F9 and you see
{"100 ";"";"150 ";"";"200 "}

Delete { and }

Hit Enter

Hope this helps

M.

 

[Marcelo Branco]

oops...

The solution above works only in Excel versions that use semicolon ; as argument separator

If your Excel version uses comma (,) as argument separator i think you need to TRANSPOSE the IF part.

Maybe this (NOT TESTED)
=SUBSTITUTE(TRIM(CONCATENATE(TRANSPOSE(IF(A6:A10=A2,B6:B10&" ",""))))," ",", ")

Highlight the red part and press F9
Delete { and }
Enter

M.