Transposing Formula

New Contributor


Can someone please assist with a formula duplicating attached/below example of going from a vertical list of data to a horizontal grouping?




5 Replies


In D1


And then in E1


and fill down.

to avoid the extra "0" and such you can replace the ranges A:A and B:B with actual range where the data is or use SORT to force the "0" at the end and ignore it


@mtarler Thank you for your help! I just attempted to duplicate but getting an error highlighting FILTER:



@KC371 what version of Excel do you have?  When you type =UN does the excel show you a list of potential functions that include UNIQUE?

@KC371 , It seems that you are having old version of Excel.

For my own use i had made a macro about a decade ago. See file Table RCF3.xls  , sheet "concatenated".


Note : this was made for personal use only so it is not so much user friendly as it is possible to be made if needed to be given to others. But i hope it could help you for time being with immediate issue.


The complementary (opposite use) file Table FCR.xls is also attached.


As variant you may create PivotTable with adding data to data model


using measure