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.
--
Natalie
=MATCH(10,A1:A20,0)
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Natalie" <[email protected]> wrote in message
news:[email protected]...
> 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.
> --
> Natalie
I won't know that the number is necessarily 10. Just that it is not equal to
zero.
--
Natalie
"Bob Phillips" wrote:
> =MATCH(10,A1:A20,0)
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Natalie" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> > --
> > Natalie
>
>
>
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)
"Natalie" <[email protected]> wrote in message
news:[email protected]...
> I won't know that the number is necessarily 10. Just that it is not equal
to
> zero.
>
>
> --
> Natalie
>
>
> "Bob Phillips" wrote:
>
> > =MATCH(10,A1:A20,0)
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Natalie" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> > > --
> > > Natalie
> >
> >
> >
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.
Assuming that column B was used, the numbers can be located by
=IF(B1<>"",OFFSET(A$1,B1-1,0),"")
- formula copy that down to row 9
It gives a sort-of correct response for duplicates, albeit from the wrong row.
Hope this helps
Originally Posted by Natalie
Last edited by Bryan Hessey; 10-30-2005 at 06:20 AM.
"Natalie" wrote
> I won't know that the number is necessarily 10.
> Just that it is not equal to zero.
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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
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.
--
Natalie
"Bryan Hessey" wrote:
>
> 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
>
>
> Natalie Wrote:
> > 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.
> > --
> > Natalie
>
>
> --
> Bryan Hessey
> ------------------------------------------------------------------------
> Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
> View this thread: http://www.excelforum.com/showthread...hreadid=480372
>
>
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)
"Natalie" <[email protected]> wrote in message
news:[email protected]...
> 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.
> --
> Natalie
>
>
> "Bryan Hessey" wrote:
>
> >
> > 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
> >
> >
> > Natalie Wrote:
> > > 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.
> > > --
> > > Natalie
> >
> >
> > --
> > Bryan Hessey
> > ------------------------------------------------------------------------
> > Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
> > View this thread:
http://www.excelforum.com/showthread...hreadid=480372
> >
> >
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.
-----------
Note, if you have two header rows, (data in A3 to A22) the formulas become:
=IF(LARGE(A$3:A$22,ROW()-2)>0,MATCH(LARGE(A$3:A$22,ROW()-2),A$3:A$22,0),"")
=IF(ISERROR(SMALL(B$3:B$22,ROW()-2)),"",SMALL(B$3:B$22,ROW()-2))
=IF(C3<>"",OFFSET(A$3,C3-1,0),"")
for columns B, C and D
As mentioned before, duplicates can be a problem if you wish to use this as a part of a table search, but
=IF(AND(C3=C2,C3<>""),"Dup","")
will flag duplicates if required.
Last edited by Bryan Hessey; 10-30-2005 at 07:34 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks