How to search a table and return concatenated values based on certain criteria?

[clarky4]

Please help!

I have a table with column headers (a,b,c) and row footers (x,y,z):

a	b	c
1​	2​	3​	x
	1​	2​	y
1​		2​	z

I'm trying to lookup up all instances of each number in the table, and return text that would concatenate the corresponding footer values with the corresponding header values:

Example 1:

- if I searched for 1's, I would expect the text: "x z a, y b"

Example 2:

- if I searched for 2's, I would expect the text: "x b, y z c"

Example 3:

- if I searched for 3's, I would expect the text: "x c"

Can this be done? If so, how?

Thanks!

 

[Fluff]

How about

+Fluff v2.xlsm
	L	M	N	O
9	a	b	c
10	1	2	3	x
11		1	2	y
12	1		2	z
13
14	2	xb, yc, zc
15
Main

Cell Formulas
Range	Formula
M14	M14	=TEXTJOIN(", ",,IF(L10:N12=L14,O10:O12&L9:N9,""))

This will work in 2019, but not 2016.

 

[sandy666]

working on both versions

// Query1
let
    Search = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    UOC = Table.UnpivotOtherColumns(Source, {"d"}, "Attribute", "Value"),
    TCC = Table.CombineColumns(UOC,{"d", "Attribute"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    Group = Table.Group(TCC, {"Value"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "result", each [Count][Merged]),
    Extract = Table.TransformColumns(List, {"result", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Result = Table.NestedJoin(Search,{"search"},Extract,{"Value"},"Table",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Result, "Table", {"result"}, {"result"}),
    RC = Table.RemoveColumns(Expand,{"search"})
in
    RC

Table3					Table4
a	b	c	d		search		result
1	2	3	x		1		xa,yb,za
	1	2	y
1		2	z

 

[clarky4]

This is close, but is it possible not to repeat the header columns in the result? Or would this require a second step following the above mentioned results?

 

[sandy666]

it is not known to whom your post is...

anyway, something like this?

Table4
search		result
1		ax,z,by

Table4
search		result
2		bx,cy,z

Table4
search		result
3		cx

 

[clarky4]

sry, my post was to either you or Fluff...

...but to answer your question, something more like this:

Search: 1
Result: x z a, y b

Search: 2
Result: x b, y z c

Search: 3
Result: x c

 

[sandy666]

you mean?

Table4
search		result
1		x z a, y b

 

[clarky4]

Yes, what you have (above) is correct.

A more complete set of examples would be as follows:

TABLE:

a	b	c
1	2	3	x
	1	2	y
1		2	z

SEARCH RESULTS:

search	result
1	x z a, y b
2	x b, y z c
3	x c

I just wanted to posting using the fancy lil add-in!

 

[sandy666]

I just wanted to posting using the fancy lil add-in!

sure, no problem

here is M (not optimised) for Power Query

let
    Search = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    UOC = Table.UnpivotOtherColumns(Source, {"d"}, "Attribute", "Value"),
    ExtractD = Table.TransformColumns(Table.AddColumn(Table.Group(UOC, {"Attribute", "Value"}, {{"Count", each _, type table}}), "Custom", each List.Distinct([Count][d])), {"Custom", each Text.Combine(List.Transform(_, Text.From), " "), type text}),
    TCC = Table.CombineColumns(ExtractD,{"Custom", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    RC = Table.RemoveColumns(TCC,{"Count"}),
    ExtractR = Table.TransformColumns(Table.AddColumn(Table.Group(RC, {"Value"}, {{"Count", each _, type table}}), "result", each [Count][Merged]), {"result", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    RC1 = Table.RemoveColumns(ExtractR,{"Count"}),
    TNJ = Table.NestedJoin(Search,{"search"},RC1,{"Value"},"Query1",JoinKind.LeftOuter),
    TSC = Table.SelectColumns(Table.ExpandTableColumn(TNJ, "Query1", {"result"}, {"result"}),{"result"})
in
    TSC

Table3					Table4
a	b	c	d		search		result
1	2	3	x		2		x b, y z c
	1	2	y
1		2	z

change the name of the tables in the code according to yours

 

[clarky4]

I guess today's the day I learn about Power Query!

At first pass, this looks to be doing exactly what I hoped it would.

Many thanks!