Formula Help

[ststern45]

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!!

 

[ststern45]

Here is an example:

Cell B8 = 699

Cells P8, Q8, R8 contain the 3 digits 6 9 9

Cell C8 has the correct value which is 9. In other words in took 9 previous cell rows for all 3 digits 6 & 9 & 9 to appear.

Cell AB8 contains the value 13 which is incorrect.

Thanks!!

 

[Eric W]

That's helpful, but I have another question. Why is the answer for B8 = 9? As I understand it, we start looking for the 699 in the row below, or row 9. The 6 we find in Q9, the first 9 we find in R17, the second 9 we find in R21. When you say the answer should be 9, it appears that you stopped counting when you saw the 9 in R17 (row 17 - row 8 = 9), but didn't your original post state that in case of a duplicate digit, you need to keep going down until you find as many digits as are in the duplicates? Or (row 21 - row 8) = 13?

Also, the C19 value puzzles me. Your value says 6, but the first 0 doesn't occur until 8 lines later (Q27). No duplicates in this line to consider. Also, C21 should be 6, not 8?

 

[ststern45]

Your correct. My apology. I posted the wrong set.
Take a look at the 262 in cell B22. Should be 5 (C22) not 6 (AB22)
As I mentioned in earlier post the sets with 2 duplicate digits at certain times calculate incorrectly.
Thanks,
Steve

 

[ststern45]

Disregard previous post.
Sorry
Your correct about the calculations.
I have to check the other Double calculations.

 

[Eric W]

No problem, let me know what you find. Incidentally, here's the formula I used in AC4:

=IFERROR(MATCH(3,MMULT(--(COUNTIF(OFFSET(P5:R5,0,0,ROW(P5:P50)-ROW(P5)+1),P4:R4)>=COUNTIF(P4:R4,P4:R4)),{1;1;1}),0),"Not enough found")

I found it a bit easier to use the OFFSET instead of the INDIRECT.

 

[ststern45]

Eric, works beautifully!!
Thank you so much for all your help!!
Super!!!
Steve

 

[Eric W]

Cool! Glad it works for you! Have a great weekend!

 

[ststern45]

Eric,
You have the same!!

 

[ststern45]

Have an add on question:
Is it possible to change the formula below that counts 3 digits to count just 1 digit
=IFERROR(MATCH(3,MMULT(--(COUNTIF(OFFSET(P5:R5,0,0,ROW(P5:P50)-ROW(P5)+1),P4:R4)>=COUNTIF(P4:R4,P4:R4)),{1;1;1}),0),"")

And also 2 digits?

I tried changing the formula to to following but some cells are blank:
1 digit formula below:

=IFERROR(MATCH(1,MMULT(--(COUNTIF(OFFSET(P5:R5,0,0,ROW(P5:P50)-ROW(P5)+1),P4:R4)>=COUNTIF(P4:R4,P4:R4)),{1;1;1}),0),"")

Thanks,
Steve

 

[Eric W]

Are you saying that you want to find the first row where you find at least one of the numbers, or the first row where you've found at least two of the numbers? In any combination or order? That may take a different approach, I'll have to think about it.