Excel formula to find position of the contents of a cell within a column.

A

al_mac75

Hi, can anyone help with the following problem. I'm trying to find out
the position of the contents of a cell within a column. I know that the
value in cell B103 appears 4 times in column B from a COUNTIF function;
however, I need to know if the value in B103 is the first, second,
third or fourth time this value has appeared. Is there an easy way to
do this using formula? I need to set a calculation and the number of
OFFSETs required will depend on whther the value in B103 is the 1st,
2nd etc. Any help would be appreciated. Thanks. Alasdair.
 
B

Bob Phillips

you could try conditional formatting with a three conditions and three
formulae,

=COUNTIF($A$1:$A1,$A1)=2

=COUNTIF($A$1:$A1,$A1)=3

=COUNTIF($A$1:$A1,$A1)>3

and separate pattern colours for each. The first instance wouldn't be
highlighted, the second, third and any more would.
 
A

al_mac75

Thanks - I'm looking for a formula though as I need to automate a
calculation. I will have up to seven values that are equal in one
column (B), I will have the column sorted so that the same values are
in subsequent rows and I then need to make a calculation that will use
offsets according to the order that the data appears. eg. cells
B103-B106 contain "B03MM40". I know from a COUNTIF function that there
are only 4 instances of this value in column B. I need to know if the
value in cell B103 is the first, second, third etc instance so I can
subtract the information from N104 from N103. My intention was to use
an OFFSET function which would allow me to automate this spreadsheet. I
could OFFSET these manually but the data in cell B103 will change and
could occur anything from one to seven times so the position would be
crucial for any OFFSET formula to work. If I can find a way of
ascertaining the position of the data in the series, I can combine this
with the number of times the data appears overall to create a nested IF
formula using OFFSETS to subtract the data from other rows ie. subtract
N105 and N104 from N103 if B103 was the first time the value appeared
in column B and there were 3 instances of the value, but only to
subtract N104 from N103 if there were 2 instances of the value. Any
ideas ?
 
A

al_mac75

Someone gave me the answer - case closed !

a =COUNTIF(A$1:A1,A1) Result = 1
b =COUNTIF(A$1:A2,A2) Result = 1
c =COUNTIF(A$1:A3,A3) Result = 1
a =COUNTIF(A$1:A4,A4) Result = 2
b =COUNTIF(A$1:A5,A5) Result = 2
b =COUNTIF(A$1:A6,A6) Result = 3
b =COUNTIF(A$1:A7,A7) Result = 4
a =COUNTIF(A$1:A8,A8) Result = 3
a =COUNTIF(A$1:A9,A9) Result = 4
c =COUNTIF(A$1:A10,A10) Result = 2
 
Top