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]

Hi Eric,

I'm using the formula you provided and it works to perfection for 3 digits.
Using the same idea and calculating when just 1 digit appears instead of 3.

Also using the same idea and calculating when 2 digits appears instead of 3.

Have a nice weekend!!

Thanks,
Steve

 

[Eric W]

This formula will tell you the offset where the first value appears:

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

with CSE.

The formula that handles 2 items is much trickier due to possible duplicates, and the trick I used for 3 columns won't work for 2. If I figure it out, I'll let you know.

 

[Eric W]

You might be better off with a UDF (User-Defined Function). I wrote one up you can try. To give it a shot, open a copy of your workbook. Press Alt-F11 to open the VBA editor. From the menu, select Insert > Module. In the sheet that opens, paste this code:

Public Function HowFar(ByVal MyRange As Range, ByVal MyCount As Long)
Dim MyData As Variant, i As Long, j As Long, k As Long

    MyData = MyRange.Value
    For i = 2 To UBound(MyData)
        For j = 1 To 3
            For k = 1 To 3
                If MyData(i, j) = MyData(1, k) Then
                    MyCount = MyCount - 1
                    MyData(1, k) = "x"
                    If MyCount = 0 Then
                        HowFar = i - 1
                        Exit Function
                    End If
                    Exit For
                End If
            Next k
        Next j
    Next i
    
    HowFar = "Not enough found"
End Function

Press Alt-Q to close the editor. Now you can use the function as shown:

	A	B	C	K	L	M	N	O	P	Q	R	S	AA	AB	AC	AD
3													1st match	2nd match	3rd match
4	6/26/2007	954	5		6/26/2007		954		9	5	4		1	1	5
5	6/25/2007	359	2		6/25/2007		359		3	5	9		1	1	2
6	6/24/2007	903	9		6/24/2007		903		9	0	3		1	2	9
7	6/23/2007	365	3		6/23/2007		365		3	6	5		1	2	3
8	6/22/2007	699	9		6/22/2007		699		6	9	9		1	9	13
9	6/19/2007	463	9		6/19/2007		463		4	6	3		1	2	9
10	6/19/2007	576	5		6/19/2007		576		5	7	6		1	4	5
11	6/19/2007	447	9		6/19/2007		447		4	4	7		1	8	9
12	6/18/2007	872	2		6/18/2007		872		8	7	2		1	1	2
13	6/17/2007	822	3		6/17/2007		822		8	2	2		1	2	3
14	6/16/2007	276	5		6/16/2007		276		2	7	6		1	2	5
15	6/15/2007	25	3		6/15/2007		25		0	2	5		1	3	3
16	6/14/2007	685	8		6/14/2007		685		6	8	5		1	1	8
17	6/13/2007	869	4		6/13/2007		869		8	6	9		3	4	4
18	6/12/2007	320	4		6/12/2007		320		3	2	0		1	3	4
19	6/11/2007	74	6		6/11/2007		74		0	7	4		1	1	8
20	6/10/2007	674	4		6/10/2007		674		6	7	4		2	3	4
21	6/9/2007	389	8		6/9/2007		389		3	8	9		3	4	6
22	6/8/2007	262	5		6/8/2007		262		2	6	2		1	1	5
23	6/7/2007	224	8		6/7/2007		224		2	2	4		3	3	8
24	6/6/2007	785	5		6/6/2007		785		7	8	5		2	4	5
25	6/5/2007	0	14		6/5/2007		999		9	9	9		4	5	7
26	6/4/2007	247	6		6/4/2007		247		2	4	7		5	5	6
27	6/3/2007	603	12		6/3/2007		603		6	0	3		1	1	12
28	6/2/2007	356	6		6/2/2007		356		3	5	6		1	5	6
29	6/1/2007	689	1		6/1/2007		689		6	8	9		1	1	1
30	5/31/2007	968	4		5/31/2007		968		9	6	8		2	2	4
31	5/30/2007	712	8		5/30/2007		712		7	1	2		3	4	8
32	5/29/2007	694	5		5/29/2007		694		6	9	4		1	3	5
33	5/28/2007	955	12		5/28/2007		955		9	5	5		3	5	8
34	5/27/2007	813	4		5/27/2007		813		8	1	3		1	3	4
35	5/26/2007	473	9		5/26/2007		473		4	7	3		1	1	9
36	5/25/2007	439	5		5/25/2007		439		4	3	9		1	2	5
37	5/24/2007	163	7		5/24/2007		163		1	6	3		3	4	7
38	5/23/2007	485	12		5/23/2007		485		4	8	5		3	4	Not enough found
39	5/22/2007	2	5		5/22/2007		2		0	0	2		1	1	5
40	5/21/2007	62	6		5/21/2007		62		0	6	2		3	4	6
41	5/20/2007	935	4		5/20/2007		935		9	3	5		1	2	4
42	5/19/2007	889	7		5/19/2007		889		8	8	9		1	4	4
43	5/18/2007	932	9		5/18/2007		932		9	3	2		2	2	Not enough found
44	5/17/2007	71	19		5/17/2007		71		0	7	1		3	Not enough found	Not enough found
45	5/16/2007	529	4		5/16/2007		529		5	2	9		Not enough found	Not enough found	Not enough found
46	5/15/2007	886	7		5/15/2007		886		8	8	6		Not enough found	Not enough found	Not enough found

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet15​

Worksheet Formulas Cell Formula AA4 =howfar($P4:$R50,1) AB4 =howfar($P4:$R50,2) AC4 =howfar($P4:$R50,3) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[ststern45]

Excellent Eric.
Works to perfection.
Thank you so much.
Steve

 

[Eric W]

Happy to help.