Company name list

[excel_learnerz]

Hey guys,

so basically I have a list on the left of companys (company a, company b as example) and list of products for each company (P1, P2, P3) and the revenue
Now I need something like the right hand side where all the products for each company are in a single cell (or even how to have them horizontally and I can concatenate them after) I can use a subtotal on the revenue anyway so I know how to do that bit,

Would anyone know how to get the products horizontally, my list is several thousand lines long,
Thanks in advance,


[TABLE="width: 484"]
<colgroup><col><col span="3"><col><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Product[/TD]
[TD]Revenue[/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]All products[/TD]
[TD]Total Revenue[/TD]
[/TR]
[TR]
[TD]Company A[/TD]
[TD]P1[/TD]
[TD="align: right"]200[/TD]
[TD][/TD]
[TD]Company A[/TD]
[TD] P1, P2, P3[/TD]
[TD="align: right"]600[/TD]
[/TR]
[TR]
[TD]Company A[/TD]
[TD]P2[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD]Company B [/TD]
[TD]P1, P2, P3[/TD]
[TD="align: right"]1200[/TD]
[/TR]
[TR]
[TD]Company A[/TD]
[TD]P3[/TD]
[TD="align: right"]300[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company B[/TD]
[TD]P1[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company B[/TD]
[TD]P2[/TD]
[TD="align: right"]800[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company B[/TD]
[TD]P3[/TD]
[TD="align: right"]300[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 79"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][TABLE="width: 79"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>

 

[AlanY]

Re: Help with company name list

try this

Book1
	A	B	C	D	E	F	G	H	I	J
1	Name	Product	Revenue		Name	All products
2	Company A	P1	200		Company A	P1	P2	P3	P4
3	Company A	P2	100		Company B	P1	P2	P3
4	Company A	P3	300
5	Company B	P1	100
6	Company B	P2	800
7	Company B	P3	300
8	Company A	P4	300
Sheet4

Cell Formulas
Range	Formula
F2	{=IFERROR(INDEX($B$2:$B$8,SMALL(IF($A$2:$A$8=$E2,ROW($A$2:$A$8)-ROW($B$1)),COLUMN(F$1)-COLUMN($E$1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[sandy666]

Re: Help with company name list

you can try PowerQuery (Get&Transform)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Name[/td][td=bgcolor:#5B9BD5]Product[/td][td=bgcolor:#5B9BD5]Revenue[/td][td][/td][td=bgcolor:#70AD47]Name[/td][td=bgcolor:#70AD47]All Products[/td][td=bgcolor:#70AD47]Total Revenue[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Company A[/td][td=bgcolor:#DDEBF7]P1[/td][td=bgcolor:#DDEBF7]

200​

[/td][td][/td][td=bgcolor:#E2EFDA]Company A[/td][td=bgcolor:#E2EFDA]P1, P2, P3[/td][td=bgcolor:#E2EFDA]

600​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Company A[/td][td]P2[/td][td]

100​

[/td][td][/td][td]Company B[/td][td]P1, P2, P3[/td][td]

1200​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Company A[/td][td=bgcolor:#DDEBF7]P3[/td][td=bgcolor:#DDEBF7]

300​

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

[tr=bgcolor:#FFFFFF][td]Company B[/td][td]P1[/td][td]

100​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Company B[/td][td=bgcolor:#DDEBF7]P2[/td][td=bgcolor:#DDEBF7]

800​

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

[tr=bgcolor:#FFFFFF][td]Company B[/td][td]P3[/td][td]

300​

[/td][td][/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}, {"Total Revenue", each List.Sum([Revenue]), type number}}),
    List = Table.AddColumn(Group, "All Products", each List.Distinct(Table.Column([Count],"Product"))),
    Extract = Table.TransformColumns(List, {"All Products", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    Reorder = Table.ReorderColumns(Extract,{"Name", "Count", "All Products", "Total Revenue"})
in
    Reorder[/SIZE]

 

[excel_learnerz]

Re: Help with company name list

guys thanks, worked great

 

[sandy666]

Re: Help with company name list

You are welcome

Have a nice day