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'

Calculating High Card Points
D1
NORTH	CELL 1	CELL 2	CELL 3	CELL 4
SPADES	4	0	0	0	0	3	0	0	0	0	0	0	0	0	0	0	7	H		8		8113			#NAME ?	UnBalanced
HEARTS	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	C		1
CLUBS	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	1	P		1
DIAMONDS	0	3	0	0	0	0	2	0	0	0	0	0	0	0	0	0	5	13		3

<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> </tbody>

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" . !!