i got this problem and would like to solve it, but i need yo

LMF

Board Regular
Joined
Mar 16, 2002
Messages
73
i got the following list, is there any way that i can have the largest number at the top and all the #N/A at the bottom, i tried so many times and it still won't work.
Please let me know if there is a way to overcome this problem:

15
117
151
152
215
237
249
266
270
279
345
361
481
573
660
880
1510
#N/A
#N/A
#N/A
#N/A
#N/A
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Your initial sort put the #N/A at the bottom.

Select the range of just numbers and sort
descending.
 
Upvote 0
1. Sort the list in Ascending order
2. Select the list and choose Edit | Go To... Special Constants Numbers (checked only)
3. Sort the resultant selection in Descending order.
 
Upvote 0
the method works, but still not quite to solve my problem, my actual problem is this:

A 1510
B 660
C 880
D 249
E 573
F 481
G 266
h #N/A
I 345
j #N/A
k 361
l 270
m 117
n 279
o 215
p 151
q 152
r 237
s 15
t #N/A
u #N/A
v #N/A


can u do a sort on this? the largest number at the top... pls help
 
Upvote 0
i did, i have tried it , but it doesn't work to my actual problem...

A 1510
B 660
C 880
D 249
E 573
F 481
G 266
h #N/A
I 345
j #N/A
k 361
l 270
m 117

I can't get it sort, because it only select the number, but i need the letter next to it to be selected as well, so that i can have it sort.

the thing is i can't get it to do it, i need to save this as a macro, but excel can only read me selecting A2: B18 range, not the thing that i want...

pls help
 
Upvote 0
Replace #N/A with blanks, sort descending, then re-enter #N/A in the blank cells.

I trust this won't change the 'actual problem'! :)
This message was edited by Mark W. on 2002-04-10 15:58
 
Upvote 0
how do u do it as a command, so that i can do it in my macro as well

can any ppl pls help me please??

:)
This message was edited by LMF on 2002-04-10 16:01
 
Upvote 0
Ensure that you select all relevant columns (A and B?) and sort as mentioned above with primarly sort on Column B.
 
Upvote 0
On 2002-04-10 15:59, LMF wrote:
how do u do it as a command, so that i can do it in my macro as well

can any ppl pls help me please??

/board/images/smiles/icon_smile.gif
This message was edited by LMF on 2002-04-10 16:01

Range("B1:B22").Select
Selection.SpecialCells(xlCellTypeConstants, 16).Select
Selection.ClearContents
Range("B1").Select
Selection.Sort Key1:=Range("B1"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("B1:B22").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "#N/A"
 
Upvote 0
i did what he said...
first select both columns A and B, sort Ascending to column B, then select constant as number only. it only select all the number on column B...

now, since i need to do sort with conlumn A as well, i select column A, but the problem is, i need to set this as a macro... and the excel can only read me doing it as manually.

means that it reads range("A2:B18"), which is not what i want in my macro. what happen if there are more #N/A, or less #N/A in the list???

so i really would like some1 can show me how this can be done???

pls let me know
:(
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top