Transposing Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1863023%22%20slang%3D%22en-US%22%3ETransposing%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1863023%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3ECan%20someone%20please%20assist%20with%20a%20formula%20duplicating%20attached%2Fbelow%20example%20of%20going%20from%20a%20vertical%20list%20of%20data%20to%20a%20horizontal%20grouping%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22KC371_0-1604702719865.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F232069iEC228BF7F61B8C52%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22KC371_0-1604702719865.png%22%20alt%3D%22KC371_0-1604702719865.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1863023%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1863252%22%20slang%3D%22en-US%22%3ERe%3A%20Transposing%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1863252%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F465841%22%20target%3D%22_blank%22%3E%40KC371%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20D1%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DUNIQUE(A%3AA)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EAnd%20then%20in%20E1%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DTEXTJOIN(%22%2C%20%22%2C%2CFILTER(B%3AB%2CA%3AA%3DD1))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eand%20fill%20down.%3C%2FP%3E%3CP%3Eto%20avoid%20the%20extra%20%220%22%20and%20such%20you%20can%20replace%20the%20ranges%20A%3AA%20and%20B%3AB%20with%20actual%20range%20where%20the%20data%20is%20or%20use%20SORT%20to%20force%20the%20%220%22%20at%20the%20end%20and%20ignore%20it%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1867028%22%20slang%3D%22en-US%22%3ERe%3A%20Transposing%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1867028%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3BThank%20you%20for%20your%20help!%20I%20just%20attempted%20to%20duplicate%20but%20getting%20an%20error%20highlighting%20FILTER%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Capture.JPG%22%20style%3D%22width%3A%20361px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F232320i8E3127E474DD1EF9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Capture.JPG%22%20alt%3D%22Capture.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New 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
Highlighted

@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

 

Highlighted

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

 

Capture.JPG

Highlighted

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

Highlighted

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

Highlighted

@KC371 

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

image.png

using measure

image.png