Excel Formula to Transpose Comma Separated Cells into One Column

[anthonyexcel]

Good afternoon,

I was looking for a formula that would give me a unique column of comma separated cells: Please see below. Thanks in advance!!!

Book4 A B C D E 1 Names Don 2 Don, Jake, Mike, Jeff, Brian Jake 3 Jake, Dianny, Sandra, Brian, Mark, Jeff Mike 4 Sally, Don, Ben, Ken, Jeff, Brian Jeff 5 Ray, Ted, Jake, Mark Brian 6 Dianny 7 Sandra 8 Mark 9 Sally 10 Ben 11 Ken 12 Ray 13 Ted Sheet1

 

[sandy666]

Names		Names
Don, Jake, Mike, Jeff, Brian		Don
Jake, Dianny, Sandra, Brian, Mark, Jeff		Jake
Sally, Don, Ben, Ken, Jeff, Brian		Mike
Ray, Ted, Jake, Mark		Jeff
		Brian
		Dianny
		Sandra
		Mark
		Sally
		Ben
		Ken
		Ray
		Ted

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Names", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Names"),
    Distinct = Table.Distinct(Table.TransformColumns(Split,{{"Names", Text.Trim, type text}}))
in
    Distinct

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Fluff]

If you have the LET function, how about

+Fluff 1.xlsm
	A	B	C
1	Names
2	Don, Jake, Mike, Jeff, Brian		Don
3	Jake, Dianny, Sandra, Brian, Mark, Jeff		Jake
4	Sally, Don, Ben, Ken, Jeff, Brian		Mike
5	Ray, Ted, Jake, Mark		Jeff
6			Brian
7			Dianny
8			Sandra
9			Mark
10			Sally
11			Ben
12			Ken
13			Ray
14			Ted
15
Main

Cell Formulas
Range	Formula
C2:C14	C2	=LET(Ary,TRIM(MID(SUBSTITUTE(A2:A5,",",REPT(" ",100)),SEQUENCE(,5,,100),100)),Cols,COLUMNS(Ary),Seq,SEQUENCE(ROWS(Ary)*Cols,,0),Indx,INDEX(Ary,Seq/Cols+1,MOD(Seq,Cols)+1),UNIQUE(FILTER(Indx,Indx<>"")))
Dynamic array formulas.

 

[anthonyexcel]

Ahh!!! I do have Let! Thank you both for your help!! I appreciate both techniques to solve the issue!!!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[sandy666]

You are welcome

 

[Peter_SSs]

I appreciate both techniques to solve the issue!!!

Are you sure that formula as-is solves the issue?

I believe that formula will only work reliably for up to 5 names per cell. Although it worked for your sample and there are 6 names in A3 & A4 it was just lucky that the 6th names, Jeff & Brian, appeared elsewhere as well. Try changing that Jeff in A3 to Jeffrey or add "Ann" to A3 as well.

This could be (partially) overcome by increasing the SEQUENCE value to something greater than the maximum expected number of names in a single cells. eg
=LET(Ary,TRIM(MID(SUBSTITUTE(A2:A5,",",REPT(" ",100)),SEQUENCE(,20,,100),100)),Cols,COLUMNS(Ary),Seq,SEQUENCE(ROWS(Ary)*Cols,,0),Indx,INDEX(Ary,Seq/Cols+1,MOD(Seq,Cols)+1),UNIQUE(FILTER(Indx,Indx<>"")))

However, if the length of the text in a single cell could be reasonably large, then errors could also occur due to the use of 100 spaces in the formula to separate the names. Here is an example where I have used 20 as shown above but with only 11 names in one cell the results are not correct.

21 02 01.xlsm
	A	B	C
1	Names
2	FirstName1, FirstName2, FirstName3, FirstName4, FirstName5, FirstName6, FirstName7, FirstName8, FirstName9, FirstName10, FirstName11		FirstName1
3	Jake, Dianny, Sandra, Brian, Mark, Jeff		FirstName2
4			FirstName3
5			FirstName4
6			FirstName5
7			FirstName6
8			FirstName7
9			FirstName8
10			FirstName9
11			F
12			irstName10
13			FirstName11
14			Jake
15			Dianny
16			Sandra
17			Brian
18			Mark
19			Jeff
20
List

Cell Formulas
Range	Formula
C2:C19	C2	=LET(Ary,TRIM(MID(SUBSTITUTE(A2:A5,",",REPT(" ",100)),SEQUENCE(,50,,100),100)),Cols,COLUMNS(Ary),Seq,SEQUENCE(ROWS(Ary)*Cols,,0),Indx,INDEX(Ary,Seq/Cols+1,MOD(Seq,Cols)+1),UNIQUE(FILTER(Indx,Indx<>"")))
Dynamic array formulas.

This second issue could also be improved by increasing the 100 values.
However, here is an alternative formula that overcomes both the issues above, but may introduce another one - mentioned below.

21 02 01.xlsm
	A	B	C
1	Names
2	Don, Jake, Mike, Jeff, Brian		Don
3	Jake, Dianny, Sandra, Brian, Mark, Jeff		Jake
4	Sally, Don, Ben, Ken, Jeff, Brian		Mike
5	Ray, Ted, Jake, Mark		Jeff
6			Brian
7			Dianny
8			Sandra
9			Mark
10			Sally
11			Ben
12			Ken
13			Ray
14			Ted
15
List (2)

Cell Formulas
Range	Formula
C2:C14	C2	=LET(s,","&TEXTJOIN(",",1,SUBSTITUTE(A2:A5,", ",","))&",",q,SEQUENCE((LEN(s)-LEN(SUBSTITUTE(s,",","")))-1), UNIQUE(REPLACE(LEFT(s,FIND("|",SUBSTITUTE(s,",","|",q+1))-1),1,FIND("|",SUBSTITUTE(s,",","|",q)),"")))
Dynamic array formulas.

The possible issue with my formula is if the list of all the names in column A, joined by commas, was longer than 32,767 characters. In that case TEXTJOIN would throw an error & the formula would not work.

So a few things for you to consider at least.

 

[anthonyexcel]

Thanks Peter_SSs for being thorough. I was going to try it today on a larger data set. I appreciate you looking over the formula and guiding me to the potential issues. Always appreciate any help that I can get. Thank you very much!

 

[Peter_SSs]

Thanks Peter_SSs for being thorough. I was going to try it today on a larger data set. I appreciate you looking over the formula and guiding me to the potential issues. Always appreciate any help that I can get. Thank you very much!

You're welcome. Glad to contribute.