Search code examples
excelexcel-formulapivot-table

How to convert duplicated data to transposed data

I create a dataset with 2 columns, there are names in column 1 and name data is duplicate itself. Column 2 contain different scores of names. I want to convert data like screenshoot. Is there any way to do it? Excel

I tried creating a pivot table from transposed data but it’s not working.

Solution

  • Perhaps try something along the lines:

    enter image description here

    • Formula used in cell A13

    =LET(array,A2:A11,
Col,COUNTIF(A2:A11,A2:A11),
VSTACK(HSTACK($A$1,$B$1&SEQUENCE(,MAX(Col))),
IFERROR(UNIQUE(HSTACK(A2:A11,MAKEARRAY(ROWS(array),MAX(Col),
LAMBDA(r,c,INDEX(FILTER(B2:B11,array=INDEX(array,r)),c))))),"Nan")))

    The above task can also be performed quite elegantly using Power Query

    enter image description here

    Follow the steps to use Power Query:

    • First convert the source range into a table and name it accordingly, for this example I have named it as Table1

    • Next, open a blank query from Data Tab --> Get & Transform Data --> Get Data --> From Other Sources --> Blank Query

    • The above lets the Power Query window opens, now from Home Tab --> Advanced Editor --> And paste the following by removing whatever you see, and press Done

    M-Code

    let
    Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Score", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Score", each Text.Combine([Score],","), type nullable text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Score", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Score.1", "Score.2", "Score.3", "Score.4", "Score.5"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter",null,"Nan",Replacer.ReplaceValue,{"Score.1", "Score.2", "Score.3", "Score.4", "Score.5"})
in
    #"Replaced Value"

    enter image description here

    • Lastly, to import it back to Excel --> Click on Close & Load or Close & Load To --> The first one which clicked shall create a new sheet with the required output while the latter will prompt a window asking you where to place the result.