Removing duplicates and sorting within cell

[louisli_evo]

Hi All,

I got another challenge with Excel.

I had a cell containing data like this:

A.5.9, A.5.9, A.5.10, A.5.11, A.5.12, A.5.13, A.5.10, A.7.10, A.7.10, A.7.10, A.5.15, A.5.15, A.5.16, A.5.18, A.8.2, A.5.17, A.5.18, A.5.18, A.5.17, A.8.3, A.8.5, A.5.17, A.8.18,A.8.15, A.8.15, A.8.15, A.8.17, A.8.20, A.8.21, A.8.22, A.5.14, A.5.14, A.5.14, A.6.6, A.5.19, A.5.20, A.5.21, A.5.22, A.5.22, A.5.24, A.6.8, A.6.8, A.5.25, A.5.26, A.5.27, A.5.28

I need to:

1. remove duplicates within the cell
2. Sort each item so they will show in increasing order (e.g. A.5.9, A.5.10, A.5.11, A.5.12...)

I can do it cell by cell manually:

1. Copy the cell contents to a blank sheet (or blank column, whatever) as a row
2. Copy and paste transpose (as a column)
3. Data --> Remove duplicates
4. Sort
   • Text to columns, delimiter, "comma", "space"
   • Sort by column 1, then column 2, then column 3
5. Combine the 3 columns using formula (e.g. D1=A1&B1&C1)
6. (Then I have no idea how to put them back in the same cell, perhaps copy and paste into notepad, remove the spaces, then paste back to the original Cell)

But I wanted to save time for these hundreds of rows, is there any smarter ways to convert them?

Thank you.

 

[Peter_SSs]

I can do it cell by cell manually:

1. Copy the cell contents to a blank sheet (or blank column, whatever) as a row

Can you explain that in more detail. If you copy the contents of a single cell and paste it elsewhere wouldn't you still have a single cell?

 

[louisli_evo]

Can you explain that in more detail. If you copy the contents of a single cell and paste it elsewhere wouldn't you still have a single cell?

Ahh, thanks, I missed something

1. Copy the cell contents to a blank sheet (or blank column, whatever) as a row
2. Text to columns, delimiter, ","
3. Copy and paste transpose (as a column)
4. Data --> Remove duplicates
5. Sort
   • Text to columns, delimiter, ".", "space"
   • Sort by column 1, then column 2, then column 3
6. Combine the 3 columns using formula (e.g. D1=A1&B1&C1)
7. (Then I have no idea how to put them back in the same cell, perhaps copy and paste into notepad, remove the spaces, then paste back to the original Cell)

 

[ISY]

Hi

Excel 365 Beta
=TEXTJOIN(", ",,BYROW(SORT(WRAPROWS(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",,SUBSTITUTE(UNIQUE(TEXTSPLIT(A1,,", ")),".","</s><s>"))&"</s></t>","//s"),3),{1\2\3}),LAMBDA(x,TEXTJOIN(".",,x))))

 

[Peter_SSs]

Hi

Excel 365 Beta
=TEXTJOIN(", ",,BYROW(SORT(WRAPROWS(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",,SUBSTITUTE(UNIQUE(TEXTSPLIT(A1,,", ")),".","</s><s>"))&"</s></t>","//s"),3),{1\2\3}),LAMBDA(x,TEXTJOIN(".",,x))))

That formula gives me one of the "There's a problem with this formula." messages.

@louisli_evo
Is your sample data accurate? I'm asking because in most places there is a space after the comma, but not here.

 

[ISY]

Hi

Sub FormulaVba()
    ActiveCell.Formula = "=TEXTJOIN("", "",,BYROW(SORT(WRAPROWS(FILTERXML(""<t><s>""&TEXTJOIN(""</s><s>"",,SUBSTITUTE(UNIQUE(TEXTSPLIT(A1,,"", "")),""."",""</s><s>""))&""</s></t>"",""//s""),3),{1,2,3}),LAMBDA(x,TEXTJOIN(""."",,x))))"
End Sub

 

[Peter_SSs]

6. Combine the 3 columns using formula (e.g. D1=A1&B1&C1)
7. (Then I have no idea how to put them back in the same cell, perhaps copy and paste into notepad, remove the spaces, then paste back to the original Cell)

Step 6 omits the "." between sections. If you wanted to stick with the manual method (pending other options), try

6. D1: =TEXTJOIN(".",,A1:C1)
7. Result: =TEXTJOIN(", ",,D1:D31)

 

[Peter_SSs]

I'm asking because in most places there is a space after the comma, but not here.

If there is always a space after the comma, this is my suggestion assuming

• Beta version with latest TEXT functions,
• Single letter prefix (like all samples)
• Single-digit whole number part (like all samples)
• Max 2-digit decimal part (like all samples)

louisli_evo.xlsm
	A	B
1	A.5.9, A.5.9, A.5.10, A.5.11, A.5.12, A.5.13, A.5.10, A.7.10, A.7.10, A.7.10, A.5.15, A.5.15, A.5.16, A.5.18, A.8.2, A.5.17, A.5.18, A.5.18, A.5.17, A.8.3, A.8.5, A.5.17, A.8.18, A.8.15, A.8.15, A.8.15, A.8.17, A.8.20, A.8.21, A.8.22, A.5.14, A.5.14, A.5.14, A.6.6, A.5.19, A.5.20, A.5.21, A.5.22, A.5.22, A.5.24, A.6.8, A.6.8, A.5.25, A.5.26, A.5.27, A.5.28	A.5.9, A.5.10, A.5.11, A.5.12, A.5.13, A.5.14, A.5.15, A.5.16, A.5.17, A.5.18, A.5.19, A.5.20, A.5.21, A.5.22, A.5.24, A.5.25, A.5.26, A.5.27, A.5.28, A.6.6, A.6.8, A.7.10, A.8.2, A.8.3, A.8.5, A.8.15, A.8.17, A.8.18, A.8.20, A.8.21, A.8.22
Sheet1

Cell Formulas
Range	Formula
B1	B1	=LET(ts,TEXTSPLIT(A1,", "),TEXTJOIN(", ",,UNIQUE(SORTBY(ts,TEXTBEFORE(ts,".",-1)&"."&TEXT(TEXTAFTER(ts,".",-1),"00")),1)))

 

[louisli_evo]

That formula gives me one of the "There's a problem with this formula." messages.

@louisli_evo
Is your sample data accurate? I'm asking because in most places there is a space after the comma, but not here.

View attachment 73982

Thanks. I just copy from one of the cells, sometimes I made a mistake and forgot to add a space after a comma, for visual separation. You're correct, there should be a space following a comma

 

[louisli_evo]

Hi

Sub FormulaVba()
    ActiveCell.Formula = "=TEXTJOIN("", "",,BYROW(SORT(WRAPROWS(FILTERXML(""<t><s>""&TEXTJOIN(""</s><s>"",,SUBSTITUTE(UNIQUE(TEXTSPLIT(A1,,"", "")),""."",""</s><s>""))&""</s></t>"",""//s""),3),{1,2,3}),LAMBDA(x,TEXTJOIN(""."",,x))))"
End Sub

Thanks for helping, I will test with my sheet