Can this be done?

[Anthon]

I have 2 columns as below, I want to leave column B(time column) in the same
order but want to sort column A(order column) by the lowest to the highest
time in column B, also zero's must be ignored, i.e 78.50 should return 1

A B

1 79.94
2 79.08
3 79.70
4 79.20
5 79.63
6 0.00
7 0.00
8 0.00
9 80.96
10 78.50
11 80.88
12 0.00
13 0.00
14 0.00
15 0.00
16 80.20
17 82.86
18 81.00

 

[Alan]

Not sure if it can be done as you ask, but the RANK function may help. In
column C try :-

=RANK(C1,$C$1:$C$18,1)

This will rank 78.5 as 8 as you have 7 zeros, it may give you a start.

Regards,
Alan.

 

[Max]

This play should get you there ..

Source data, viz order#s, qty are assumed in A1:B1 down
In C1: =IF(B1>0,B1+ROW()/10^10,"")
In D1:
=IF(ROWS($1:1)>COUNT($C:$C),"",INDEX(A:A,MATCH(SMALL($C:$C,ROWS($1:1)),$C:$C,0)))
Copy D1 to E1. Select C1:E1, copy down to cover the max expected extent of
source data. Hide/minimize col C. Cols D & E will return the desired results
(order#-Qty) with orders auto-sorted by qty (ascending), and zero qty's
omitted. Ties ie duplicate qty (if any) are handled as well.

Success ? Click the YES button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik

 

[Max]

Oops. Minor clarification. In my response, think I mis-described your source
col B figs as "qty" instead of "time". As long as col B contains real
numbers, its label is immaterial. The set-up should work fine.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik
---

 

[DILipandey]

Hi Anthon,

Considering you have the data in column A and B, try the following formula
in column C and drag it till the end:-

=LARGE(A:A,RANK(B1,B:B))

--
Click on Yes, if it is useful.

Thanks & Best Regards,
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India

 

[Anthon]

Wow, what a quick response, thanks.

Tried your play, but the lowest time returns 10 instead of 1, also, i have
another sting in the tail, sometimes i have a #N/A in time column as well(see
B19 & B20), i need these ignored too. Here result from your play(i included
row F to show you what result should be):

A B C D E F

1 79.94 79.94 10 78.50 6
2 79.08 79.08 2 79.08 2
3 79.70 79.70 4 79.20 5
4 79.20 79.20 5 79.63 3
5 79.63 79.63 3 79.70 4
6 0.00 1 79.94
7 0.00 16 80.20
8 0.00 11 80.88
9 80.96 80.96 9 80.96 9
10 78.50 78.50 18 81.00 1
11 80.88 80.88 17 82.86 8
12 0.00
13 0.00
14 0.00
15 0.00
16 80.20 80.20 7
17 82.86 82.86 11
18 81.00 81.00 10
19 #N/A
20 #N/A

Regards
Anthon

 

[Max]

Tried your play, but the lowest time returns 10 instead of 1 ..

That's because col D retrieves the order nums in col A corresponding to the
auto-sorted results, which was what I thought you *really*wanted. You can
replace col D with a simple auto-serial numbering, like this:
In D1, copied down: =IF(E1="","",ROWS($1:1))

.. sometimes i have a #N/A in time column as well(see
B19 & B20), i need these ignored too

Then we have to increase the criteria checking's strength
ie front-check it for real nums in col B using ISNUMBER

Put this instead in C1, copy down:
=IF(ISNUMBER(B1),IF(B1>0,B1+ROW()/10^10,""),"")

The above should take care of it
C'mon, gimme a wave. Click the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik
---

 

[Anthon]

Almost there, remember in my 1st post I want to keep my time column B in the
same order, thats why I need the result to be in the format I have in F so I
can see at a glance which time is the lowest, 2nd lowest, 3rd lowest, etc....
i.e column B and F will be next to each other when the other columns hidden.

B F

79.94 6
79.08 2
79.70 5
79.20 3
79.63 4
0.00
0.00
0.00
80.96 9
78.50 1
80.88 8
0.00
0.00
0.00
0.00
80.20 7
82.86 11
81.00 10
#N/A
#N/A

Thanks for your prompt response yet again.
Anthon

 

[Max]

Let's start over
With your source data in A1:B18
In C1: =IF(ISNUMBER(B1),IF(B1>0,B1,""),"")
In D1: =IF(C1<>"",RANK(C1,$C$1:$C$18,1),"")
Copy C11 down to D18
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik

 

[Anthon]

Fantastic!!!!!!!!!! is it posible then to combine the 2 formula(C1 and D1)
into 1 formula?

Regards
Anthon

 

[Harlan Grove]

I have 2 columns as below, I want to leave column B(time column) in the same
order but want to sort column A(order column) by the lowest to the highest
time in column B, also zero's must be ignored, i.e 78.50 should return 1

....

Meaning col B remains as-is, but col A should show the ascending order
of the entries in col B? If so, you could use the following array
formula

A1 [array formula]:
=IF(COUNTIF(B1,">0"),COUNT(1/(B1>$B$1:$B$20)/($B$1:$B$20>0))+1,"")

Fill A1 down as far as needed.

 

[Max]

.. combine the 2 formula (C1 and D1)

into 1 formula?

See Harlan's response. Effectively done.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik
---