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]

I found this formula but calculates incorrectly when duplicate numbers are used. For example, if P4 = 0, Q4 = 0, R4 = 0 or P4 = 4, Q4 = 4, R4 = 7.
=MATCH(TRUE,MMULT((COUNTIF(OFFSET($P5:$R$215,,,ROW($P5:$R$215)-ROW($P5)+1),$P4:$R4)>0)+0,TRANSPOSE(COLUMN($P5:$R$215)^0))=3,0)

 

[ststern45]

I added a helper column with the following formula in cell AA4
=CHOOSE(MAX(INDEX(COUNTIF($P4:$R4,$P4:$R4),)),"NM","D","T")

NM = All 3 digits are different (no-match)
D = 2 of the 3 digits are the same (doubles)
T = 3 of the 3 digits are the same (triples)

The NM formula calculates correctly:
=IF($AA4="NM",MATCH(TRUE,MMULT((COUNTIF(OFFSET($P5:$R$215,,,ROW($P5:$R$215)-ROW($P5)+1),$P4:$R4)>0)+0,TRANSPOSE(COLUMN($P5:$R$215)^0))=3,0))
(ctrl+shift+enter)

The T (triples) calculates correctly:
=IF(AA4="T",MATCH(TRUE,MMULT((COUNTIF(OFFSET($P5:$R$215,,,ROW($P5:$R$215)-ROW($P5)+1),$P4:$R4)>2)+0,TRANSPOSE(COLUMN($P5:$R$215)^0))=3,0))

Need help with the formula below:

The D (doubles) calculates some sets correctly and others incorrectly:
=IF($AA4="D",MATCH(TRUE,MMULT((COUNTIF(OFFSET($P5:$R$215,,,ROW($P5:$R$215)-ROW($P5)+1),$P4:$R4)>1)+0,TRANSPOSE(COLUMN($P5:$R$215)^0))=3,0))
(ctrl+shift+enter)

 

[ststern45]

I wanted to bump this thread and ask using this information:

The D (doubles) calculates some sets correctly and others incorrectly:
=IF($AA4="D",MATCH(TRUE,MMULT((COUNTIF(OFFSET($P5:$R$215,,,ROW($P5:$R$215)-ROW($P5)+1),$P4:$R4)>1)+0,TRANSPOSE(COLUMN($P5:$R$215)^0))=3,0))
(ctrl+shift+enter)

Thanks!!

 

[Eric W]

A few days late, so maybe you've found something, but this formula seems to do what you want without a helper column:

=IFERROR(SMALL(IF(MMULT(--(COUNTIF(INDIRECT("P5:R"&ROW(P5:P11)),P4:R4)>=COUNTIF(P4:R4,P4:R4)),{1;1;1})=3,ROW(P5:P11)-ROW(P5)+1),1),"Not enough found")

with Control+Shift+Enter. Handles no duplicates, 1 duplicate, or all 3 are the same.

 

[Eric W]

A bit shorter:

=IFERROR(MATCH(3,MMULT(--(COUNTIF(INDIRECT("P5:R"&ROW(P5:P11)),P4:R4)>=COUNTIF(P4:R4,P4:R4)),{1;1;1}),0),"Not enough found")

with CSE.

 

[ststern45]

Thanks Eric. Unfortunately the results are not calculating correctly.
This is a link to the Excel file in question:
https://my.pcloud.com/publink/show?code=XZUkWK7ZOHr6lwSAf1VCmPHmLXGzNzrYxLz7

I added an additional worksheet and added a new formula. The only problem is that the formula does NOT calculate for "doubles". The 6-way formula works, the triples formula works, the doubles formula works for some doubles but not others??

Column "AB" contains the formula for Doubles, Triples, and Doubles. I added a helper column AA.

As I mentioned above the "doubles" works sometime. It seems it all depends on what the previous results and how the numbers are drawn by position.

Thanks,
Steve

 

[Eric W]

My organization doesn't allow me to download files. Saying that "the results are not calculating properly" doesn't really help me much. Can you give an example where it doesn't work?

 

[ststern45]

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

 

[ststern45]

Cell column C has the correct values. There are no formulas that created these values.

Cell column AA has a helper that determines what 3 digit value type. D = Double (2 digits are the same), T = Triple (3 digits are the same), NM = No-Match (No digits are the same)

Cell column AB I attempted to create formulas based on the "type" of the value in cell column B. The formulas for T Triples and NM No-Match work correctly but the D Double formula only works on certain double sets.

I hope posting the image and the information above clears up the questions.

Thanks,
Steve