Alphabetical Sort and Concatenate

[Greenthumbherblady]

Hello Everyone... is there a way to Alphabetical Sort then concatenate?

For example: If I have TA TT BA BB can excel alphabetical sort all combo then concatenate?

TA TT TA_TT
BA TA TT BA_TA_TT

Thanks much

Theresa

 

[mole999]

would those be individual columns
concatenate, will they all be in the cell when you finish, or have the concatenate in its own column

 

[VBA Geek]

for the above sample you can use the below VBA function






Excel 2013/2016

	A	B
BA TA TT	BA_TA_TT
TA TT	TA_TT

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=SORT_AND_CONCAT(A1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=SORT_AND_CONCAT(A2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]




code to add:

Dim objArrL As Object

Function SORT_AND_CONCAT(sInp As String) As String
If objArrL Is Nothing Then Set objArrL = CreateObject("system.collections.arraylist")
Dim s
With objArrL
    .Clear
    For Each s In Split(sInp)
        .Add s
    Next
    .Sort
    SORT_AND_CONCAT = Join(.toarray, "_")
End With
End Function

if you dont know how to add code: https://www.excelguru.ca/content.php?265-Adding-VBA-Code-For-The-First-Time-User

 

[Greenthumbherblady]

individual columns then concatenate in its own

 

[mole999]

maybe I read this wrong

if that was a to c then in D > =A1&"_"&B1&"_"&C1

and sort on D maybe

 

[VBA Geek]

individual columns then concatenate in its own

I thought you had the input all in the same cell. Then don't use my solution.

 

[Rick Rothstein]

Hello Everyone... is there a way to Alphabetical Sort then concatenate?

For example: If I have TA TT BA BB can excel alphabetical sort all combo then concatenate?

TA TT TA_TT
BA TA TT BA_TA_TT

It is hard to tell from your post, but I am assuming the text you want to sort and concatenate are located in individual cells, not all in one cell separated by spaces. Given that, below is a UDF (user defined function) which will do what you want. The function takes two required argument (the range of cells whose values you want to sort and concatenate and the text delimiter you want to put between them) and one optional argument (which allows you to reverse the sort from ascending to descending by passing in TRUE for it). Also note that this function ignores empty cells within the specified range.

[table="width: 500"]
[tr]
	[td]Function CellSorter(Rng As Range, Delimiter As String, Optional ReverseIt As Boolean) As String
  Dim Cell As Range
  With CreateObject("System.Collections.ArrayList")
    For Each Cell In Rng
      If Len(Cell.Value) Then .Add Application.Trim(Cell.Value)
    Next
    .Sort
    If ReverseIt Then .Reverse
    CellSorter = Join(.ToArray, Delimiter)
  End With
End Function
[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use CellSorter just like it was a built-in Excel function. For example,

=CellSorter(A2:C2,"_")

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.C

 

[Rick Rothstein]

It is hard to tell from your post, but I am assuming the text you want to sort and concatenate are located in individual cells, not all in one cell separated by spaces. Given that, below is a UDF (user defined function) which will do what you want. The function takes two required argument (the range of cells whose values you want to sort and concatenate and the text delimiter you want to put between them) and one optional argument (which allows you to reverse the sort from ascending to descending by passing in TRUE for it). Also note that this function ignores empty cells within the specified range.

[table="width: 500"]
[tr]
	[td]Function CellSorter(Rng As Range, Delimiter As String, Optional ReverseIt As Boolean) As String
  Dim Cell As Range
  With CreateObject("System.Collections.ArrayList")
    For Each Cell In Rng
      If Len(Cell.Value) Then .Add Application.Trim(Cell.Value)
    Next
    .Sort
    If ReverseIt Then .Reverse
    CellSorter = Join(.ToArray, Delimiter)
  End With
End Function
[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use CellSorter just like it was a built-in Excel function. For example,

=CellSorter(A2:C2,"_")

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.C

Maybe a better name for the function would be SortConcat....

[table="width: 500"]
[tr]
	[td]Function SortConcat(Rng As Range, Delimiter As String, Optional ReverseIt As Boolean) As String
  Dim Cell As Range
  With CreateObject("System.Collections.ArrayList")
    For Each Cell In Rng
      If Len(Cell.Value) Then .Add Application.Trim(Cell.Value)
    Next
    .Sort
    If ReverseIt Then .Reverse
    SortConcat = Join(.ToArray, Delimiter)
  End With
End Function
[/td]
[/tr]
[/table]

So the example would become...

=SortConcat(A2:C2,"_")