Multiple column value

[Thomas Kuriakose]

Respected Sirs,

I am trying to learn PQ and tried a sample to match multiple values for one lookup value as in the attached file.

Test1 tab Table 1 has the list of sellers and the respective products. Table 2 has only list of sellers.

The desired out put from power query is as in K3:N6.

The results are coming in pivot table, but we need the text of products in values field (Test2 tab).

Kindly guide on this,

thank you very much,

with regards,
thomas

 

[AlanSidman]

Would this presentation work for you?

Data Range

	A​	B​	C​	D​	E​	F​
3​	Count of Product​	Product​	​	​	​	​
4​	Sellers​	Apples​	Apricot​	Bananas​	Lemons​	Oranges​
5​	Adam​	​	​	1​	1​	1​
6​	Robert​	​	1​	​	1​	​
7​	Sally​	1​	​	1​	​	1​
8​	Tom​	1​	1​	1​	​	​

If yes, then the following shows the Pivot Table set up

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for your guidance on this question.

This will work, but we prefer the desired result as in K3:N6.

I tried a formula approach, but I was just thinking whither we can get a result in any other form.
=IFERROR(INDEX(Table1[Product],SMALL(IF($A2=Table1[Sellers],ROW(Table1[Sellers])- MIN(ROW(Table1[Sellers]))+1,""),COLUMN()-5)), "")

Thank you very much for your kind support,

with regards,
thomas

Seller	Column1	Column2	Column3	Column4
Sally	Oranges	Bananas	Apples
Robert	Apricot	Lemons
Adam	Bananas	Oranges	Lemons
Tom	Apples	Apricot	Bananas

 

[bosco_yip]

Here is a formula approach (not power query) for your reference.

1] Your "Seller" desired result in K3:K6 appear alphabetically sorted, but your post #.03 file formula output in E2:E5 appear not in order.

So,

2] I put my "Formula result (with alphabetically sorted in seller name) in the range of P2:S7

3] In "Seller" P3, copied down :

=IFERROR(INDEX(A$2:A$12,MATCH(0,MMULT(COUNTIF(A$2:A$12,"<"&A$2:A$12),1)-SUM(MMULT(COUNTIF(A$2:A$12,P$2: P2),1)),0)),"")

4] In "Product" Q3, copied across and down :

=IF($P3="","",IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($A$2:$A$12)/($A$2:$A$12=$P3),COLUMNS($A:A))),""))

Regards
Bosco

 

[Thomas Kuriakose]

Respected Sir,

Thank you so very much for this insight and your solution.

Thank you very much once again,

with regards,
thomas