Transposing Formula

Copper Contributor

Hello,

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

 

KC371_0-1604702719865.png

 

5 Replies

@KC371 

In D1

=UNIQUE(A:A)

And then in E1

=TEXTJOIN(", ",,FILTER(B:B,A:A=D1))

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:

 

Capture.JPG

@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.

@KC371 

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

image.png

using measure

image.png