Numbering items from a list in order

N

Natalie

Does someone have a method for identifying a set of numbers by their position
within in a list.
For example
0,0,0,10,0,0,5,0,2
If I want to identify all the items that are not equal to zero, by their
position
10 would return 4, 5 would return 7, and 2 would return 9
I need to identify every occurance.
 
B

Bob Phillips

=MATCH(10,A1:A20,0)

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

But you said 10 would return 4, 5 would return 7. You could not get 7 for 5
unless you knew there was a 5 there.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
B

Bryan Hessey

Try

=IF(LARGE(A$1:A$9,ROW())>0,MATCH(LARGE(A$1:A$9,ROW()),A$1:A$9,0),"")

and formula-copy down to row 9

It will present the position required, but in descending sort order.

Hope this helps
 
M

Max

I won't know that the number is necessarily 10.
Just that it is not equal to zero.
Click to expand...

Just another guess

Assuming your list is in A1 down

Put in B1: =IF(A1<>0,ROW(A1),"")
Copy B1 down until the last row of data in col A
 
N

Natalie

Thanks Bryan, So near yet so far.
unfortunately its a bad example because whilst your formula works
brilliantly for this example (i.e. the numbers are in descending order) if I
swap the numbers 5 and 2 around in the list, 2 is still 9 using your formula
instead of its new position 7, and 5 is still 7 instead of its new position 9.
Any ideas? This ones really stumped me.
 
B

Bob Phillips

Natalie,

Isn't that because it is referring to the rank, not the number. So when 2 is
in position 9, the third largest is 9, when it is in position 7, the 2nd
largest is in in 9?

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
B

Bryan Hessey

Natalie,

If you want to know the sequence in which they occur, then after the
formula in column B

=IF(LARGE(A$1:A$9,ROW())>0,MATCH(LARGE(A$1:A$9,ROW()),A$1:A$9,0),"")

use

=SMALL(B$1:B$9,ROW())
or
=IF(ISERROR(SMALL(B$1:B$9,ROW())),"",SMALL(B$1:B$9,ROW()))

and then use the offset on that column

=IF(C1<>"",OFFSET(A$1,C1-1,0),"")

Also, a

=COUNTIF(B1:B9,">0")

will tell you how many numbers there are.
 
Top