Transpose by keeping unique values as header item

Question

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.

Answer 1

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:

---

• 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

---

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"

---

---

• 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:

---

---

• 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

---