Combine multiple cells with comma in alphabetical order

slohman

Board Regular
Joined
Mar 31, 2012
Messages
110
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Could you post your sample data as a table (including the result as expected)?
You can post your data as a table using XL2BB add-in, you can find it here:
 
Upvote 0
You'll surely get a better answer than this one, since my VBA is still very crude...but, I'm learning and this worked. My only issue was that the cells N - Z had to be alphanumeric starting with a letter.
This might get you started until a VBA expert gives a much better solution.

Code:
Sub SpecialSort()
Dim i As Integer, R As String, j As Integer
Range("N2:Z2").Formula = "=Countif($N$1:$Z$1,""<=""&N1)"
R = ""
For i = 1 To 13
Cells(2, 28) = i
j = Application.WorksheetFunction.Match(Application.WorksheetFunction.Small(Range("N2:Z2"), Cells(2, 28)), Range("N2:Z2"), 0)
Cells(2, 27) = j
R = R & Cells(1, Cells(2, 27) + 13) & ","
Next
R = Left(R, Len(R) - 1)
Cells(1, 27) = R
Rows("2").EntireRow.Delete
End Sub
 
Upvote 0
Sure I need it to sort across the rows and in a descending order
sample.JPG
 
Upvote 0
Your posted sample may be too small to read. In my lame example, you can change "Small" to "Large" to change the order.
 
Upvote 0
That is still too small for me to read or work out exactly what is in each cell.
Even if I could, I probably wouldn't bother typing it all out to test. What about trying the suggestion made in post 2?
Failing that you could upload a small sample file (with results) to, for example, Dropbox or OneDrive, and provide a shared link here. Note though that some helpers have workplace security restrictions that will prevent them from accessing such an uploaded file. XL2BB doesn't have that problem.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top