Function that sorting rows and paste the result in one cell

One way:

=SUMPRODUCT(LARGE($A$1:$F$1,ROW(INDIRECT("1:6"))) *
10^(6-ROW(INDIRECT("1:6"))))


In article ,
stakar wrote:

I have the following columns

A B C D E F
-------------
0 1 2 0 1 1

i need a function that, to sort the data by asceding order and then to
concatenate the result.

So using the above will get the following
G
-------
211100

Is it possible ??
Thanks in advance
Stathis

PS.
If the above is possible, may i have another function too, that
excluding the zeros so to get the

G
-----
2111

instead?

Thanks

Hi
you may try the following user defined function:

Function sort_concatenate(rng As Range)
Dim arr As Variant
Dim holder
Dim ret_str
Dim i
Dim j
arr = rng.Value

For i = LBound(arr, 2) To UBound(arr, 2) - 1
For j = i + 1 To UBound(arr, 2)
If arr(1, i) arr(1, j) Then
holder = arr(1, i)
arr(1, i) = arr(1, j)
arr(1, j) = holder
End If
Next j
Next i

For i = LBound(arr, 2) To UBound(arr, 2)
ret_str = ret_str & arr(1, i)
Next
sort_concatenate = ret_str
End Function

---
if you want zeors included change the last part of this function to
For i = LBound(arr, 2) To UBound(arr, 2)
if arr(1,i) 0 then
ret_str = ret_str & arr(1, i)
end if
Next

--
Regards
Frank Kabel
Frankfurt, Germany

"stakar " schrieb im
Newsbeitrag ...
I have the following columns

A B C D E F
-------------
0 1 2 0 1 1

i need a function that, to sort the data by asceding order and then
to
concatenate the result.

So using the above will get the following
G
-------
211100

Is it possible ??
Thanks in advance
Stathis

PS.
If the above is possible, may i have another function too, that
excluding the zeros so to get the

G
-----
2111

instead?

Thanks


---
Message posted from http://www.ExcelForum.com/

"JE McGimpsey" schrieb im Newsbeitrag
...
One way:

=SUMPRODUCT(LARGE($A$1:$F$1,ROW(INDIRECT("1:6"))) *
10^(6-ROW(INDIRECT("1:6"))))

Hi JE
nice one :-)

Frank

Thanks a lot
You both were helpfull!

I was "playing" with both solutions !!

Thanks once more!


---
Message posted from http://www.ExcelForum.com/