Excel Data Transformation-CodePudding

I have data in the existing table as shown here and I want the my table look like the expected table after transformation.

Existing Table

Desired Table

CodePudding user response：

You may use the below formula to accomplish the query, assuming you are using O365, and when writing this formula, you have enabled the Office Insiders, Beta Channel Version then,

• Formula used in cell D2

=LET(array,A3:A9,
Col,COUNTIF(A3:A9,A3:A9),
VSTACK(HSTACK($A$2,MAKEARRAY(1,MAX(Col),LAMBDA(r,c,$B$2&c))),
IFERROR(UNIQUE(HSTACK(A3:A9,MAKEARRAY(ROWS(array),MAX(Col),
LAMBDA(r,c,INDEX(FILTER(B3:B9,array=INDEX(array,r)),c))))),"")))

Perhaps if you are using Excel 2021/MS365, then try the one below,

• Formula used in cell D3

=UNIQUE(A3:A9)

• Formula used in cell E3

=TRANSPOSE(FILTER(B3:B9,D3=A3:A9))

If you are not using the above Excel Versions, then try the following

• Formula used in cell D11

=IFERROR(INDEX($A$3:$A$9,MATCH(0,COUNTIF($D$10:D10,$A$3:$A$9),0)),"")

For the above formula you need to fill down and may need to press CTRL SHIFT ENTER based on your excel version.

• Formula used in cell E11

=IFERROR(INDEX($B$3:$B$9,AGGREGATE(15,6,
(ROW($B$3:$B$9)-ROW($B$3) 1)/($D11=$A$3:$A$9),COLUMN(A1))),"")

And Fill Down & Fill Across!

CodePudding user response：

You can also obtain your desired output using Power Query, available in Windows Excel 2010 and Office 365 Excel

Select some cell in your original table
Data => Get&Transform => From Table/Range or From within sheet
When the PQ UI opens, navigate to Home => Advanced Editor
Make note of the Table Name in Line 2 of the code.
Replace the existing code with the M-Code below
Change the table name in line 2 of the pasted code to your "real" table name
Examine any comments, and also the Applied Steps window, to better understand the algorithm and steps

M Code

let

//Change next line to reflect actual data source (table name)
    Source = Excel.CurrentWorkbook(){[Name="Table16"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"State", type text}, {"City", type text}}),

//Group by Staate
//  Extract each States cities as a delimited list
//  Add a Count aggregation so we know how many columns to create
    #"Grouped Rows" = Table.Group(#"Changed Type", {"State"}, {
        {"City", each Text.Combine([City],";"), type text},
        {"Count", each List.Count([City]), Int64.Type}}),

//Split the city column into the required number of columns
//Then delete the Count column
    #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "City", 
        Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), List.Max(#"Grouped Rows"[Count])),    
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Count"}),
    
//Rename the city columns to replace the generated dot with a space, as you show in your output example
    renameList = List.Transform(List.RemoveFirstN(Table.ColumnNames(#"Removed Columns"),1), each {_, Text.Replace(_,"."," ")}),
    #"Rename City Columns" = Table.RenameColumns(#"Removed Columns", renameList)


in
    #"Rename City Columns"