request for help

[Peter3580]

I have a series of numbers in a cells in a column e.g. 5632
4361
3343
2434
3352
I need a formula that will put the numbers into descending numerical order e.g.
6532
6431
4333
4332
5332
I am really new to excel and don't even know if this is possible - its easy to do without a formula but would be nice

 

[Dryver14]

you could try the following in the next column

=LARGE(H:H,ROWS($H$1:H1))

Obviously use the column you want, and copy down as to actually changing the numbers in place, I don't think that is possible without vba

 

[MickG]

If you can use this UDF it should do the job

Function Srt(R [COLOR="Navy"]As[/COLOR] Range) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] i, x() [COLOR="Navy"]As[/COLOR] Byte
x = StrConv(R, vbFromUnicode)
[COLOR="Navy"]For[/COLOR] i = 0 To UBound(x)
    Srt = Srt & Chr$(Application.Large(x, i + 1))
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] Function

To Save and Run UDF:-
Copy Function from Thread
In Your Data sheet , Click "Alt+F11",:- Vb Window appears.
From the VBWindow toolbar, Click "Insert" ,"Module":- New VBwindow appears .
Paste Code into this window.
Close Vbwindow.

In sheet, Add the "function" in cell "B1"as:- =Srt(A1)
NB:- Once you've added the first bracket you can select the range "A1" from the sheet
Then click "Enter" to show result
Drag Function down remaining numbers



Regards Mick

 

[Peter3580]

The UDF worked a treat thanks

 

[Peter3580]

Hi Mick Actually it worked on a trial sheet but not in my 'real'

[TABLE="width: 643"]
<colgroup><col width="92" style="width: 69pt; mso-width-source: userset; mso-width-alt: 2717;"> <col width="26" style="width: 20pt; mso-width-source: userset; mso-width-alt: 768;"> <col width="23" style="width: 18pt; mso-width-source: userset; mso-width-alt: 689;" span="16"> <col width="30" style="width: 23pt; mso-width-source: userset; mso-width-alt: 886;"> <col width="11" style="width: 9pt; mso-width-source: userset; mso-width-alt: 334;"> <col width="23" style="width: 18pt; mso-width-source: userset; mso-width-alt: 689;"> <col width="23" style="width: 18pt; mso-width-source: userset; mso-width-alt: 689;"> <col width="49" style="width: 37pt; mso-width-source: userset; mso-width-alt: 1457;"> <col width="14" style="width: 11pt; mso-width-source: userset; mso-width-alt: 413;"> <col width="13" style="width: 10pt; mso-width-source: userset; mso-width-alt: 393;"> <col width="83" style="width: 63pt; mso-width-source: userset; mso-width-alt: 2461;"> <col width="102" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3012;"> <tbody>[TR]
[TD="class: xl86, width: 516, bgcolor: transparent, colspan: 19"]Calculating High Card Points[/TD]
[TD="class: xl65, width: 11, bgcolor: transparent"][/TD]
[TD="class: xl88, width: 23, bgcolor: transparent"][/TD]
[TD="class: xl86, width: 23, bgcolor: transparent"][/TD]
[TD="class: xl86, width: 49, bgcolor: transparent"][/TD]
[TD="class: xl86, width: 14, bgcolor: transparent"][/TD]
[TD="class: xl86, width: 13, bgcolor: transparent"][/TD]
[TD="class: xl86, width: 83, bgcolor: transparent"][/TD]
[TD="width: 102, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]D1[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl89, bgcolor: transparent"][/TD]
[TD="class: xl85, bgcolor: transparent"][/TD]
[TD="class: xl85, bgcolor: transparent"][/TD]
[TD="class: xl85, bgcolor: transparent"][/TD]
[TD="class: xl85, bgcolor: transparent"][/TD]
[TD="class: xl85, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]NORTH[/TD]
[TD="class: xl93, bgcolor: transparent, colspan: 4"]CELL 1[/TD]
[TD="class: xl93, bgcolor: transparent, colspan: 4"]CELL 2[/TD]
[TD="class: xl93, bgcolor: transparent, colspan: 4"]CELL 3[/TD]
[TD="class: xl93, bgcolor: transparent, colspan: 4"]CELL 4[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl89, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl83, bgcolor: transparent"][/TD]
[TD="class: xl83, bgcolor: transparent"][/TD]
[TD="class: xl86, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]SPADES[/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl70, bgcolor: transparent"]H[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl90, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl88, bgcolor: transparent"]8113[/TD]
[TD="class: xl91, bgcolor: transparent"][/TD]
[TD="class: xl84, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"]#NAME ?[/TD]
[TD="class: xl92, bgcolor: transparent"]UnBalanced[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]HEARTS[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl77, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl71, bgcolor: transparent"]C[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl90, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl92, bgcolor: transparent"][/TD]
[TD="class: xl87, bgcolor: transparent"][/TD]
[TD="class: xl82, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]CLUBS[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl77, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl71, bgcolor: transparent"]P[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl90, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl88, bgcolor: transparent"][/TD]
[TD="class: xl92, bgcolor: transparent"][/TD]
[TD="class: xl84, bgcolor: transparent"][/TD]
[TD="class: xl86, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]DIAMONDS[/TD]
[TD="class: xl78, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl78, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl78, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl78, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl78, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl78, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl78, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl78, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl78, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl78, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl78, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl78, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl78, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl78, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl78, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl79, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl72, bgcolor: transparent"]13[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl90, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl88, bgcolor: transparent"][/TD]
[TD="class: xl92, bgcolor: transparent"][/TD]
[TD="class: xl84, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

When I use the =Srt(X4) the column where my numbers are, I get the #NAME ? error. I cant see any thing wrong with the code so what else am I doing wrong?

Peter

 

[MickG]

You're welcome

 

[MickG]

Based upon your data column "X" has 4 vertical cells with the an individual numbers in each of the 4 cells i.e :- 8,1,1,3.
The actual number in 8113 appears to be in cell "Z4" not "X4" . !!