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

[LMF]

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

 

[LMF]

it gives me a list of this: with #N/A selected:

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

what does it suppose to do ???

 

[davers5]

I would split the column in two using the text to column feature (seperate the letter from the number) and then sort them based on the number. Then you can easily write a formula and concatenate them if you like. Here is some code that will do it (minus the concatenation).

Columns(1).Select
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1))
Range(Columns(1), Columns(2)).Select
Selection.Sort Key1:=ActiveCell.Offset(0, 1).Range("A1"), Order1:= _
xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Cells(1, 1).Select

Is this what you're looking for?

Dave

 

[LMF]

Hi there, i tried your code and i got the folowing results, but this is not what i want... (did i do it right?)

Text Number

xsxvb 15
cvbvn 117
cvbvn 151
bvn 152
ncvn 215
vn 237
dfx 249
dsf 266
gwt 270
ncvb 279
fdg 345
cnnb 361
bgfhfn 481
dfghc 573
hrhcvb 660
sdfbvc 880
sfds 1510
sdfsdf #N/A
gfbcb #N/A
yrt #N/A
dfghc #N/A
ssfs #N/A

pls let me know



still waiting some more ppl to help me please?

 

[LMF]

I have just test the text to column function and it is now working the way i want it to be.

the main thing that i used is replacing all the #N/A to blanks or 0...

but thanks guys, it is working now!



u guys are nice and great

Cheers
LMF

 

[EBK]

Where is the information coming from, are you typing the information directly into the cells and if so, is the information you type derived from other info within an excel sheet?