excelexcel-formulaexcel-2010transposerepeat# Transpose by keeping unique values as header item

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.

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:

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

- Build vertical summary from Columns with Count
- Excel - Create a Unit Conversion sheet - Multiple links
- update cell when column header matches a list
- Delete text in cell subject to Worksheet_Change
- Counting the number of visible rows after autofilter
- Excel 2007: AVERAGEIF, SUMIF, COUNTIF, MAXIF, MINIF across multiple sheets, multple rows
- Import CSV data from a txt file skipping the first line and adding headers
- Simplifying SumIFs formulas for efficient excel formula
- I'd like to find out how to find the current streak of non-negative numbers in a row of data in Excel
- ValueError: Invalid character found in sheet title
- decrypt excel files
- Excel table search funcion that looks for partial string match
- Combine macros to filter on today's and tomorrow's date
- Converting line breaks to commas in excel sheet using Powershell
- Combinations of numbers arranged side by side as many as a random number (VBA)
- How can I remove ONLY leading and trailing spaces while leaving spaces in between words alone with an excel formula?
- VBA Date as integer
- Sliding Window Auto Increment Range
- Count cells with different conditional ranges
- Turning flattened pivots data into tables -Error tables can't overlap - Excel Vba
- How do I get only a specific part of a cell in Excel or Numbers?
- Calculate the Legendre symbol of two integers in Excel
- Unable to get text wrapping or vertical centering to work with xlsxwriter
- Number stored as text warning in excel using POI
- VBA Excel paste to the columns in regular intervals
- Convert filtered values from formulas to values
- Sorting Dictionary by nested class objects data [VBA]
- Events does not appear when making an Outlook query from Excel
- The script works well when I use MSXML2.XMLHTTP.6.0, but it fails miserably when I switch to MSXML2.serverXMLHTTP.6.0
- How to convert text string (d h m s) to time format in excel