Hi everyone,

Need help with creating a formula:

Digits = 0 through 9

Cells P4 through R4 are the most recent digits Position 1, Position 2, Position 3

Trying to calculate how many rows back when all 3 digits 9, 5, 4 appeared.

So using the example below. The 5 & 9 (from cell range P4 through R4) appeared on line 1. The remaining digit 4 did not appear until line 5 (Cell range P9, Q9, R9)

So the formula needed would give me a value of "5"

So I'm trying to calculate when all 3 digits from P4, Q4, R4 appear from the previous rows.

Note: A cell range could have duplicate digits. For example P4, Q4, R4 could be 9 9 4 or 5 5 5

So even though the digits repeat I would need to count the 1st occurrence of the 1st duplicate digit, 2nd digit of the duplicate or 3rd digit of the duplicate.

So for example if Cell P4 = 9, Q4 = 9, R4 = 6. The result would be: 3.

1st digit 9 appeared on line 1 Cell R5), the second digit 9 appeared on line 2 (cell P6) and the digit 6 appeared on line 3.

Cell P4 = 9

Cell Q4 = 5

Cell R4 = 4

1 Cells P5,Q5,R5 = 3 5 9

2 Cells P6,Q6,R6 = 9 0 3

3 Cells P7,Q7,R7 = 3 6 5

4 Cells P8,Q8,R8 = 6 9 9

5 Cells P9,Q9,R9 = 4 6 3

6 Cells P10,Q10,R10 = 5 7 6

7 Cells P11,Q11,R11 = 4 4 7

Thank you in advance!!

Need help with creating a formula:

Digits = 0 through 9

Cells P4 through R4 are the most recent digits Position 1, Position 2, Position 3

Trying to calculate how many rows back when all 3 digits 9, 5, 4 appeared.

So using the example below. The 5 & 9 (from cell range P4 through R4) appeared on line 1. The remaining digit 4 did not appear until line 5 (Cell range P9, Q9, R9)

So the formula needed would give me a value of "5"

So I'm trying to calculate when all 3 digits from P4, Q4, R4 appear from the previous rows.

Note: A cell range could have duplicate digits. For example P4, Q4, R4 could be 9 9 4 or 5 5 5

So even though the digits repeat I would need to count the 1st occurrence of the 1st duplicate digit, 2nd digit of the duplicate or 3rd digit of the duplicate.

So for example if Cell P4 = 9, Q4 = 9, R4 = 6. The result would be: 3.

1st digit 9 appeared on line 1 Cell R5), the second digit 9 appeared on line 2 (cell P6) and the digit 6 appeared on line 3.

Cell P4 = 9

Cell Q4 = 5

Cell R4 = 4

1 Cells P5,Q5,R5 = 3 5 9

2 Cells P6,Q6,R6 = 9 0 3

3 Cells P7,Q7,R7 = 3 6 5

4 Cells P8,Q8,R8 = 6 9 9

5 Cells P9,Q9,R9 = 4 6 3

6 Cells P10,Q10,R10 = 5 7 6

7 Cells P11,Q11,R11 = 4 4 7

Thank you in advance!!

Last edited: