Combine multiple cells with comma in alphabetical order

[slohman]

I have columns N-Z with text or numbers I need maybe in each, looking for a formula or vba to join into cell AA in alphabetical order with a comma as delimiter.

The rows can be up to 500

 

[DanteAmor]

It seems that you have something like this in cells N2, O2 and P2.
And the result is those values sorted alphabetically in cell AA2

Book1
	A													N	O	P											AA	AB
1
2														V58-1160	V81-1314	Play structure											Play structure, V58-1160, V81-1314
3
Hoja2

That you need for each row?

 

[kweaver]

I used the following function to concatenate the data from N to Z (assumes you have Excel 365 because you need TEXTJOIN).

Sub SortCells()
Dim i As Integer, str As String, LR As Long
LR = Cells(Rows.Count, "N").End(xlUp).Row
 For i = 1 To LR
 str = WorksheetFunction.TextJoin(",", True, Range("N" & i & ":Z" & i))
 Cells(i, 27) = str
 Next
End Sub

This put the concatenation in column AA

Then, in AB I used the following function (e.g., AB1 is =SortWithinCell(AA1,",", TRUE) and filled that down.)

Function SortWithinCell(CelltoSort As Range, DelimitingCharacter As String, IncludeSpaces As Boolean) As String
CelltoSortString = WorksheetFunction.Substitute(CelltoSort.Value, " ", "")
MyArray = Split(CelltoSortString, DelimitingCharacter)
    For N = 0 To UBound(MyArray)
     'For N = UBound(MyArray) To 0 Step -1
        For M = 1 To UBound(MyArray)
            
                'If MyArray(M) < MyArray(M - 1) Then
                If MyArray(M) > MyArray(M - 1) Then
                    TempValue = MyArray(M)
                    MyArray(M) = MyArray(M - 1)
                    MyArray(M - 1) = TempValue
                End If

        Next M
    Next N
For N = 0 To UBound(MyArray)
    SortWithinCell = SortWithinCell & MyArray(N) & DelimitingCharacter
Next N
SortWithinCell = Left(SortWithinCell, Len(SortWithinCell) - 1)
If IncludeSpaces = True Then SortWithinCell = WorksheetFunction.Substitute(SortWithinCell, ",", ", ")

End Function

Is that getting further along??

 

[slohman]

Suggestion 2 works but doesn't loop it only does the N1:Z1 it needs to loop all the way to the last row and it also put ,,,, where there is blanks.

Any suggestions would be great

TIA

 

[slohman]

I used the following function to concatenate the data from N to Z (assumes you have Excel 365 because you need TEXTJOIN).

Sub SortCells()
Dim i As Integer, str As String, LR As Long
LR = Cells(Rows.Count, "N").End(xlUp).Row
For i = 1 To LR
str = WorksheetFunction.TextJoin(",", True, Range("N" & i & ":Z" & i))
Cells(i, 27) = str
Next
End Sub

This put the concatenation in column AA

Then, in AB I used the following function (e.g., AB1 is =SortWithinCell(AA1,",", TRUE) and filled that down.)

Function SortWithinCell(CelltoSort As Range, DelimitingCharacter As String, IncludeSpaces As Boolean) As String
CelltoSortString = WorksheetFunction.Substitute(CelltoSort.Value, " ", "")
MyArray = Split(CelltoSortString, DelimitingCharacter)
    For N = 0 To UBound(MyArray)
     'For N = UBound(MyArray) To 0 Step -1
        For M = 1 To UBound(MyArray)
           
                'If MyArray(M) < MyArray(M - 1) Then
                If MyArray(M) > MyArray(M - 1) Then
                    TempValue = MyArray(M)
                    MyArray(M) = MyArray(M - 1)
                    MyArray(M - 1) = TempValue
                End If

        Next M
    Next N
For N = 0 To UBound(MyArray)
    SortWithinCell = SortWithinCell & MyArray(N) & DelimitingCharacter
Next N
SortWithinCell = Left(SortWithinCell, Len(SortWithinCell) - 1)
If IncludeSpaces = True Then SortWithinCell = WorksheetFunction.Substitute(SortWithinCell, ",", ", ")

End Function

Is that getting further along??

Thankyou so much.

That is working but I need it to look like this

VS8-1160 Essentials Play Structure, VS1-1314 Essentials Play Structure

 

[Peter_SSs]

I need it to look like this

VS8-1160 Essentials Play Structure, VS1-1314 Essentials Play Structure

1. That isn't alphabetical as you originally requested. Can you clarify?

2. How do we determine what gets repeated in each text? Is it the last cell with data in the row?

3. Could you please give a few more examples and results that we can read & copy? Post 2 gives you a suggestion of a good way to do that. It produces the sort of thing you see in post 11.

 

[DanteAmor]

That is working but I need it to look like this
VS8-1160 Essentials Play Structure, VS1-1314 Essentials Play Structure

Please Note
-----------------------
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).

 

[slohman]

Thanks DanteAmor.

Sorry for the confusion, I will try and explain it.

I have text in Col N1 which is seperated by a comma eg. (SE-08 Jitterbug, FS74 Matrix (loose fill), VS8-1160 Essentials Play Structure, FS145 Twirler, 5299 T Nut), I need it to sort the column into alphabetical/numeric order in descending order. I can concatenate them into individual cells (eg N1:Z1) if need be and then sort them from there, but I don’t know how to

Suggestion 2 doesn't seem to loop pass N1 so rows beyond N1 say N2:Z500 dont get sorted and it also doesn't allow for blanks.

What I really need it to look like in column AA or any other column past Z eg: (VS8-1160 Essentials Play Structure, SE Jitterbug, FS74 Matrix (loose fill), FS145 Twirler, 5299 T Nut)

I have rows from N1 down to lastrow (which could change)

 

[DanteAmor]

Ok, that's perfect, I put it in xl2bb to see it better.

Book1
	A													N													AA
1														SE-08 Jitterbug, FS74 Matrix (loose fill), VS8-1160 Essentials Play Structure, FS145 Twirler, 5299 T Nut													VS8-1160 Essentials Play Structure, SE Jitterbug, FS74 Matrix (loose fill), FS145 Twirler, 5299 T Nut
Hoja23

 

[Peter_SSs]

Try this UDF and copy down the formula shown in the worksheet below..

Function RevSort(s As String) As String
  Dim AL As Object
  Dim vPart As Variant
 
  Set AL = CreateObject("System.Collections.ArrayList")
  For Each vPart In Split(Replace(s, ", ", ","), ",")
    AL.Add vPart
  Next vPart
  AL.Sort
  AL.Reverse
  RevSort = Join(AL.ToArray, ", ")
End Function

Book1
	N													AA
1	SE-08 Jitterbug, FS74 Matrix (loose fill), VS8-1160 Essentials Play Structure, FS145 Twirler, 5299 T Nut													VS8-1160 Essentials Play Structure, SE-08 Jitterbug, FS74 Matrix (loose fill), FS145 Twirler, 5299 T Nut
Sheet3

Cell Formulas
Range	Formula
AA1	AA1	=RevSort(N1)

 

[DanteAmor]

It is not necessary to separate the text into columns, you can leave all the text in cell N1.
Try this macro for the entire N column, the result will be in column AA

Sub sort_data()
  Dim arrList As Object, a As Variant, i As Long, ary As Variant
  a = Range("N1", Range("N" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    Set arrList = CreateObject("System.Collections.ArrayList")
    For Each ary In Split(a(i, 1), ",")
      arrList.Add CStr(Trim(ary))
    Next
    arrList.Sort
    arrList.Reverse
    b(i, 1) = Join(arrList.ToArray, ", ")
  Next
  Range("AA1").Resize(UBound(a)).Value = b
End Sub