Multiple colums into a single cell

[Tony S.]

Is there a formula that can sort multiple cell in order lowest to highest,
then show the result in a single cell oriented horizontally?

I have this:
7
41
13
8
44
14
9
24
38
1
25
2
11
26
3
12
4
22
50
6
23
32
29
40
15
30
in diffenent rows. I'mtrying to get this...
1, 2, 3, 4, 6, 7, 8, 9, 11, 12, 13, 14, 15, 22, 23, 24, 25, 26, 29, 30, 32,
38, 40, 41, 44, 50
in one cell. Each number sepaated by a comma and space.
Thanks!

 

[Shane Devenshire]

Hi,

I think you are going to need to construct a VBA Function to do that. I'm
working so I can't spend the time now, but I sure someone will write one for
you its not too tough.

 

[Tony S.]

Yeah, I figured I'd probably need some code. That's what I was referring to
when I said "formula". Thanks Shane...

 

[Chip Pearson]

Try code like the following:

Sub AAA()
Dim S As String
Dim RR As Range
Dim R As Range

Set RR = Range("A1:A10")
RR.Sort key1:=Range("A1"), order1:=xlAscending
For Each R In RR.Cells
S = S & R.Text & ", "
Next R
S = Left(S, Len(S) - 2)
Range("C1").Value = S
End Sub

This sorts A1:A10 in ascending order and then writes all values as a
single text string into C1. Change ranges to meet your needs.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Tony S.]

Excellent Chip, as always.
Question: Since, the exact number of rows can always vary, is it possible to
set the range to a maximun number and not return extra spaces with commas for
blank cells? Like this?

Set RR = Range("A1:A999")

 

[Dave Peterson]

Maybe something like:

Option Explicit
Sub AAA()
Dim S As String
Dim RR As Range
Dim R As Range

Set RR = Range("A1:A10")
RR.Sort key1:=Range("A1"), order1:=xlAscending
S = ""
For Each R In RR.Cells
If R.Value = "" Then
'skip it
Else
'do the concatenation
S = S & R.Text & ", "
End If
Next R
If S = "" Then
'all blank, so do nothing
Else
S = Left(S, Len(S) - 2)
End If
Range("C1").Value = S
End Sub

 

[Tony S.]

Thank you Dave. That did the trick. Also, thanks for the remarks in the code
to help me understand.

 

[Chip Pearson]

Tony,

Replace

Set RR = Range("A1:A10")

with

With Worksheets("Sheet1")
Set RR = Range(.Cells(1, "A"), _
.Cells(.Rows.Count, "A").End(xlUp))
End With

This will set RR to a range from row 1 to the last non-empty cell in
column A.

Replace

S = S & R.Text & ", "

with

If StrComp(R.Text, vbNullString) <> 0 Then
S = S & R.Text & ", "
End If

This will skip empty cells.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Tony S.]

Fantastic Chip! Both yours and Dave solution work perfectly. I can't thank
you all enough.