Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel formula to find position of the contents of a cell within a column.
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. |
#2
|
|||
|
|||
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. -- HTH Bob Phillips wrote in message oups.com... 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. |
#3
|
|||
|
|||
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 ? |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can i get an If formula in excel to edit another cell? | Excel Worksheet Functions | |||
Why does Excel show a formula in ONE cell ? | Excel Discussion (Misc queries) | |||
Formula Arrays VERY SLOW in Excel 2002 | Excel Worksheet Functions | |||
Excel 97 - Adding Every 8th Row - Formula should work, but doesn't | Excel Worksheet Functions | |||
How do I view the actual numeric value of a formula in Excel 2002. | Excel Worksheet Functions |