Search code examples
excelexcel-formulaexcel-2010transposerepeat

Transpose by keeping unique values as header item

Excel sheet to transpose

I would like to transpose these two columns by keeping the unique values in C as header items and items in D grouped under the header groups

The header groups are transposed repeatedly instead of appearing only once.

enter image description here

Solution

  • Try the following formula, assuming there is no Excel Constraints as per the tags posted & if i have understood correctly the required output, then the given formula should work:

    enter image description here

    • Formula used in cell F1

    =LET(
     a, C2:D42,
     b, TAKE(a,,1),
     c, DROP(a,,1), 
     ub, UNIQUE(b),
     DROP(IFERROR(REDUCE("",TOROW(ub),LAMBDA(x,y,
     HSTACK(x,VSTACK(y,FILTER(c,b=y))))),""),,1))

    Update :

    OP mentions in comment :

    hey, tried it, but i get formula contains error.

    Not sure what error you are getting, but there is one more neater and cleaner approach, and easy as well using POWER QUERY

    enter image description here

    To perform the task using POWER QUERY, follow the steps:

    • First convert both the source ranges 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 M-Code by removing whatever you see, and press Done
    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Orgname"}, {{"All", each _, type table [Orgname=text, Fullname=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([All],"Index",1,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Orgname", "Fullname", "Index"}, {"Orgname", "Fullname", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[Orgname]), "Orgname", "Fullname"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

    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.

    Update as per Screenshot from OP, looks they are using Older Version, then one could try the following:

    enter image description here

    enter image description here

    • Formula used in cell D1

    =IFERROR(INDEX($A$2:$A$42,MATCH(0,INDEX(COUNTIF($C$1:C1,$A$2:$A$42),,),0)),"")

    The above formula needs to fill across. Also while exiting the edit mode, you would need to hit CTRL+SHIFT+ENTER, as these are array formulas.

    • Formula used in cell D2

    =IFERROR(INDEX($B$2:$B$42,AGGREGATE(15,6,
 (ROW($B$2:$B$42)-ROW($B$2)+1)/(D$1=$A$2:$A$42),ROWS(D$1:D1))),"")

    The above formula needs to fill down and fill right! Also while exiting the edit mode, you would need to hit CTRL+SHIFT+ENTER, as these are array formulas.

    Excel can be downloaded from here