Function that sorting rows and paste the result in one cell

  • Thread starter Thread starter stakar
  • Start date Start date
S

stakar

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 t
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, tha
excluding the zeros so to get the

G
-----
2111

instead?

Thank
 
One way:

=SUMPRODUCT(LARGE($A$1:$F$1,ROW(INDIRECT("1:6"))) *
10^(6-ROW(INDIRECT("1:6"))))
 
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
 
Thanks a lot
You both were helpfull!

I was "playing" with both solutions !!

Thanks once more!
 
Back
Top