Data Transformation Help Needed

[PNC]

Available Data
Company	Revenue
ABCD LTD	£100
ABCD LTD	£200
ABCD LTD	£300
EXCEL LTD	£500
EXCEL LTD	£400
EXCEL LTD	£300
XYZ LTD	£1,000
XYZ LTD	£1,500
XYZ LTD	£2,000
Please show how to avail the data in the required transformation as below (using formula or excel pivot feature)
Need to Transform as:
Company	Revenue
ABCD LTD	£100	£200	£300
EXCEL LTD	£500	£400	£300
XYZ LTD	£1,000	£1,500	£2,000

 

[vletm]

PNC
Do this someway belongs to Your other thread?

Data Transformation help needed

Please show how to avail the data in the required transformation as per the attached excel file (using formula or excel pivot feature)

chandoo.org

 

[AlanSidman]

Easily accomplished with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Company"}, {{"Data", each _, type table [Company=text, Revenue=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data],"Index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Revenue", "Index"}, {"Custom.Revenue", "Custom.Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Custom", {{"Custom.Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Custom", {{"Custom.Index", type text}}, "en-US")[Custom.Index]), "Custom.Index", "Custom.Revenue")
in
    #"Pivoted Column"

 

[PNC]

Thank you, Alan.
Although, not so acquainted with Power Query - will try to as suggested.
Best regards

 

[PNC]

Hi,

I tried but failed as I am not much aware of how the power query works.

If you have any idea how to do that in basic formula based way - I will much appreciate it.

Thanks once again.

 

[PNC]

PNC
Do this someway belongs to Your other thread?

Data Transformation help needed

Please show how to avail the data in the required transformation as per the attached excel file (using formula or excel pivot feature)

chandoo.org

Well, I am new to this forum - hence query was posted twice.
However, I am not acquainted with the Power query (as I had been suggested the way to solve my issue) the query/solution is still pending - if there are some other ways to sort out the matter normally.

Thank you for your heed.

 

[bosco_yip]

Here is formula solution for your consideration.

1] In D2, formula copied down :

=IFERROR(INDEX(A$2:A$10, MATCH(0,INDEX(COUNTIF(D$1:D1,A$2:A$10),0),0)),"")

2] In E2, formula copied across right and down :

=IFERROR(INDEX($B$2:$B$10,AGGREGATE(15,6,ROW($B$1:$B$9)/($A$2:$A$10=$D2),COLUMN(A1))),"")

Or,

If you have Excel 2019 or Office 365, you could use this single formula.

In D2, array formula ("Ctrl+Shift+Enter") copied across and down :

=IFERROR(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,IF($A$2:$A$10<>$A$1:$A$9,$A$2:$A$10&"</b><b>"&$B$2:$B$10&"</b><b>"&$B$3:$B$11&"</b><b>"&$B$4:$B$12,""))&"</b></a>","//b["&ROW($A1)*4+COLUMN(A$1)-4&"]"),"")

 

[PNC]

@
bosco_yip

Thank you very much.