how to alphabetize a list

[jontherev]

Is there a way to modify the following macro (graciously provided by user Alkey) so that it simultaneously alphabetizes the list as well? Actually, I would prefer a second macro, because I don't always want to alphabetize my lists. So, I guess I would need a function called something like Concat_Range_Alphabetize. As you can tell, I'm a total vba NOOB, so please forgive my ignorance.

Function Concat_Range(rngJoin As Range, strSep As String) As String
Concat_Range = Join(Filter(Application.Transpose(Evaluate("=IF(" & rngJoin.Address & "<>""""" _
& "," & rngJoin.Address & "," & """~""" & ")")), "~", False), strSep)
End Function

Thanks, I really appreciate your time!!!

[Richard Buttrey]

Hi,

I'm finding it difficult to visualise what your original data looks like (rngJoin) and what the strSep is.

Would you upload your workbook with a before and after situation which shows the result of your UDF.

[jontherev]

Sure, here's a simple example. The answer is in cell A9. My expected outcome would be:
Abraham Lincoln, George Bush, George Washington, Hillary Clinton, Paula Abdul, Stephen Colbert

The list is just random names for the purposes of this question. Thanks!

[Richard Buttrey]

Hi,

How about sorting the range first with a macro then applying your UDF

e.g.

Sub Sort()
    Range("A1:A6").Sort key1:=Range("A1")
    Range("A9") = "=concat_range(A1:A6,"","")"
End Sub

Function Concat_Range(rngJoin As Range, strSep As String) As String
Concat_Range = Join(Filter(Application.Transpose(Evaluate("=IF(" & rngJoin.Address & "<>""""" _
& "," & rngJoin.Address & "," & """~""" & ")")), "~", False), strSep)
End Function

You will probably want to use use some code in the Sort macro to identify the range but I guess you see the idea.

[jontherev]

Thanks Richard, but I couldn't get your sort macro to work. I did say I was a total NOOB on vba right? I'm sure I'm doing something wrong on my end. However, I did receive another solution (in the formulas forum) using a formula to first alphabetize the list (creating a new column of data, which I was hoping to avoid), and then applying this Concat_Range macro to that. I was just hoping there was some way to modify the code above to handle everything in one fell swoop. But, thank you for your time.

[Richard Buttrey]

Put the Procedure I gave you and the function you provided in the same Module. Just copy all the code I gave you and paste it.

Then from the ribbon use the Developer tab, choose macros and then the macro called 'Sort' and OK
The sort macro will first sort the range A1:A6 and then it will enter your concat formula

"=concat_range(A1:A6,"","")

in A9.

[jontherev]

Ah, thanks for the details! For some reason it doesn't add the space after the comma, but other than that, it works perfectly. Thanks, thread solved!