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:

Selection	PO Numbers
Josh	100, 150, 200

<tbody>
</tbody>

Name	PO Number
Josh	100
Jim	99999
Josh	150
Heather	88888
Josh	200

<tbody>
</tbody>

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)

Name	PO Number		Name	PO Number
Josh	100​		Josh	100, 150, 200
Jim	99999​		Jim	99999
Josh	150​		Heather	88888
Heather	88888​
Josh	200​

[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

	A​	B​
1​	Selection​	PO Numbers​
2​	Josh​	100, 150, 200​
3 ​
4 ​
5 ​	Name​	PO Number​
6 ​	Josh​	100​
7 ​	Jim​	99999​
8 ​	Josh​	150​
9​	Heather​	88888​
10​	Josh​	200​

<tbody>
</tbody>

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.