Formula Help

ststern45

Well-known Member
Joined
Sep 17, 2005
Messages
958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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!!
 
Last edited:

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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)
 
Upvote 0
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)
 
Upvote 0
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!!
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Excel 2010
ABCKLMNOPQRSAAAB
46/26/200795456/26/2007954954NM5
56/25/200735926/25/2007359359NM2
66/24/200790396/24/2007903903NM9
76/23/200736536/23/2007365365NM3
86/22/200769996/22/2007699699D13
96/19/200746396/19/2007463463NM9
106/19/200757656/19/2007576576NM5
116/19/200744796/19/2007447447D9
126/18/200787226/18/2007872872NM2
136/17/200782236/17/2007822822D4
146/16/200727656/16/2007276276NM5
156/15/200702536/15/2007025025NM3
166/14/200768586/14/2007685685NM8
176/13/200786946/13/2007869869NM4
186/12/200732046/12/2007320320NM4
196/11/200707466/11/2007074074NM8
206/10/200767446/10/2007674674NM4
216/9/200738986/9/2007389389NM6
226/8/200726256/8/2007262262D6
236/7/200722486/7/2007224224D9
246/6/200778556/6/2007785785NM5
256/5/2007000146/5/2007999999T7
266/4/200724766/4/2007247247NM6
276/3/2007603126/3/2007603603NM12
286/2/200735666/2/2007356356NM6
296/1/200768916/1/2007689689NM1
305/31/200796845/31/2007968968NM4
315/30/200771285/30/2007712712NM8
325/29/200769455/29/2007694694NM5
335/28/2007955125/28/2007955955D8
345/27/200781345/27/2007813813NM4
355/26/200747395/26/2007473473NM9
365/25/200743955/25/2007439439NM5
375/24/200716375/24/2007163163NM7
385/23/2007485125/23/2007485485NM9
395/22/200700255/22/2007002002D5
405/21/200706265/21/2007062062NM6
415/20/200793545/20/2007935935NM4
425/19/200788975/19/2007889889D4
435/18/200793295/18/2007932932NM9
445/17/2007071195/17/2007071071NM19
455/16/200752945/16/2007529529NM4
465/15/200788675/15/2007886886D10
PD2
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top