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!

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.


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Tony S." wrote:

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!

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

"Shane Devenshire" wrote:

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.


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Tony S." wrote:

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!

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)


On Wed, 18 Feb 2009 10:06:06 -0800, Tony S.
wrote:

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!

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")

"Chip Pearson" wrote:

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)


On Wed, 18 Feb 2009 10:06:06 -0800, Tony S.
wrote:

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!

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. wrote:

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")

"Chip Pearson" wrote:

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)


On Wed, 18 Feb 2009 10:06:06 -0800, Tony S.
wrote:

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!


--

Dave Peterson

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

"Dave Peterson" wrote:

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. wrote:

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")

"Chip Pearson" wrote:

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)


On Wed, 18 Feb 2009 10:06:06 -0800, Tony S.
wrote:

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!


--

Dave Peterson

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)


On Wed, 18 Feb 2009 12:13:01 -0800, Tony S.
wrote:

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")

"Chip Pearson" wrote:

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)


On Wed, 18 Feb 2009 10:06:06 -0800, Tony S.
wrote:

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!

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

"Chip Pearson" wrote:


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)


On Wed, 18 Feb 2009 12:13:01 -0800, Tony S.
wrote:

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")

"Chip Pearson" wrote:

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)


On Wed, 18 Feb 2009 10:06:06 -0800, Tony S.
wrote:

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!