Match Last Occurrence of two numbers and Count to Previous Occurence

[Sam via OfficeKB.com]

Hi All,

Thank you very much for taking the time to provide various formulas.

I need to find /match the LAST time two specific numbers appeared together
and Count the number of Rows between the LAST time they appeared together
back to their PREVIOUS appearance together.

I have a table of numbers that spans nine Columns (A-I)and 400+ Rows (20-
480). I have made the table a Dynamic Range called "Numbers". Each Row
contains numbers in ascending order.

Is there a formula that can check for two specific numbers Row by Row
through the (nine column) Range "Numbers" and Return the Row Count of their
LAST appearance together back to their PREVIOUS appearance together, from
the Dynamic Range "Numbers"?

Columns A-I = Dynamic Range "Numbers"
Rows 20-480

Example sample data from Range "Numbers":

ROW20 51 58 59 65 69 72 73 76 79
ROW31 50 51 58 72 73 76 79 80 81
ROW50 50 52 60 62 68 69 70 75 76
ROW75 53 54 59 60 62 69 70 72 73
ROW80 50 51 58 59 70 71 72 73 76
ROW83 51 53 65 67 68 69 78 80 81
ROW94 51 52 58 60 61 65 67 72 73
ROW99 50 53 57 62 63 68 70 71 73

Example Criteria:
Locate when 72 AND 73 LAST appeared together and Count back to their
PREVIOUS appearance together to get the required Count; i.e. the number of
Rows in between the LAST appearance and the PREVIOUS appearance. Count from
the Row above LAST appearance to the Row before PREVIOUS appearance.

Expected Result:
72 AND 73 LAST Appear=ROW99 PREVIOUS Appear=ROW94(Count From Row98 To Row95)
Count=4

50 AND 51 LAST Appear=ROW80 PREVIOUS Appear=ROW31(Count From Row79 To Row32)
Count=48

68 AND 69 LAST Appear=ROW83 PREVIOUS Appear=ROW50(Count From Row82 To Row51)
Count=32

80 AND 81 LAST Appear=ROW83 PREVIOUS Appear=ROW31(Count From Row82 To Row32)
Count=51


Regards,
Sam

 

[Biff]

Hi!

ROW99 50 53 57 62 63 68 70 71 73
72 AND 73 LAST Appear=ROW99

Is that a typo?

Also, I notice that the numbers you're looking for are
consecutive:

72 AND 73
50 AND 51
68 AND 69
80 AND 81

Is that always the case?

Biff

 

[Jason Morin]

Kludgy, but it'll work:

=MAX(IF(findnum1*findnum2<>0,ROW(Numbers)))-LARGE(IF
(findnum1*findnum2<>0,ROW(Numbers)),2)-1

Array-entered, where:

findnum1 =

COUNTIF(INDIRECT(ADDRESS(ROW(Numbers),MIN(COLUMN
(Numbers)))&":"&ADDRESS(ROW(Numbers),COLUMNS
(Numbers))),num1)

findnum2 =

=COUNTIF(INDIRECT(ADDRESS(ROW(Numbers),MIN(COLUMN
(Numbers)))&":"&ADDRESS(ROW(Numbers),COLUMNS
(Numbers))),num2)

num1 = cell containing first number
num2 = cell containing second number

HTH
Jason
Atlanta, GA

 

[Max]

Here's one simple set-up to tinker with ..

Assume the source table is in Sheet1, A20:I480

In Sheet1
---------
Put in, say, K20:

=IF(AND(ISNUMBER(MATCH(Sheet2!$A$2,A20:I20,0)),ISNUMBER(MATCH(Sheet2!$B$2,A2
0:I20,0))),ROW(),"")

Copy K20 down to K480

In Sheet2
---------
Assume the pair of numbers (e.g.: 68,69 or 80,81 etc) will be input into
A2:B2
The order of the paired inputs into A2:B2 is immaterial, can be 68,69 or
69,68, for example

Put in C2:

=IF(OR($A2="",$B2=""),"",LARGE(Sheet1!$K$2:$K$480,COLUMNS($A$1:A1)))

Copy C2 across to D2

Put in E2: =IF(OR(C2="",D2=""),"",(C2-D2)-1)

For the pair of numbers input into A2:B2 :
C2 will return the row number of the last occurrence in Sheet1
D2 will return the row number of the 2nd last occurrence in Sheet1
E2 will return the number of rows in-between the last and the 2nd last
occurrence in Sheet1

 

[Biff]

If the numbers are always consecutive, as noted in my
reply, this is much easier than all of that!

Biff

 

[Sam via OfficeKB.com]

Hi Biff,

Sorry, yes it is a typo. Should be 72 AND 73 LAST Appear=ROW94 Previous
Appear=ROW80. The numbers will always be consecutive.

Regards,
Sam

 

[Biff]

Hi!

OK.

Assume your table is in the range A20:I480.

Use a cell to hold the numbers you're looking for, say A1:

A1 = 7273

In a helper column, say column J, in J20 enter this
formula and copy down to J480. (or, just double click the
fill handle)

=IF(ISNUMBER(SEARCH
(A$1,A20&B20&C20&D20&E20&F20&G20&H20&I20)),ROW(),"")

Then, to find the number of rows between the last instance
and the previous instance:

=LARGE(J20:J480,1)-LARGE(J20:J480,2)-1

Biff

 

[Max]

For the pair of numbers input into A2:B2 :

C2 will return the row number of the last occurrence in Sheet1
D2 will return the row number of the 2nd last occurrence in Sheet1
E2 will return the number of rows in-between the last and the 2nd last
occurrence in Sheet1

The test results (below) seems to reconcile with expected results
based on the source data as originally posted,
with typo corrected for line:

ROW99 50 53 57 62 63 68 70 71 73

to be

ROW99 50 53 57 62 63 68 70 72 73

Paired inputs in A2:B2 returns in: C2 - D2 - E2
-----------------------------------------------
72,73 returns: 99 - 94 - 4
50,51 returns: 80 - 31 - 48
68,69 returns: 83 - 50 - 32
80,81 returns: 83 - 31 - 51

(Paired inputs can be in any order: 73,72 or 51,50 etc)

 

[Sam via OfficeKB.com]

Hi Biff,

The Dynamic Range "Numbers" is the main source. However, I've had to set up
a summary sheet where the consecutive numbers I'm looking for are in two
separate Columns on the same Row. Eg: 72 AND 73 in Columns A and B Row 2
respectively.

Can your formula using the SEARCH Function accommodate my summary sheet
setup.

Regards,
Sam

 

[Sam via OfficeKB.com]

Hi Jason,

Thank you for assistance. I've entered your Array Formula as suggested
keeping the FindNum1 and FindNum2 in separate manageable parts; however, I
get a #Num! error - not sure why? I've also input the Formula as one very
large Formula but still get the #Num! error.

Would appreciate further assitance with using the "Numbers" Dynamic Range.

Regards,
Sam

 

[Biff]

Hi!

Certainly!

You enter the numbers to search for in:

Summary A2 = 72
Summary B2 = 73

=IF(ISNUMBER(SEARCH(Summary!A$2&Summary!
B$2,A20&B20&C20&D20&E20&F20&G20&H20&I20)),ROW(),"")

Biff

 

[Sam via OfficeKB.com]

Hi Max,

Thanks for your assitance. I actually need the formula to find the
information your formula returns but for a complete column of paired
numbers rather than just a single Row.

The paired numbers are housed on a summary sheet in Columns A and B
starting from Row 2.

Is it possible to provide such a Formula that uses the original Dynamic
Range "Numbers" rather than referencing the source data using the A1
reference style. The numbers to find will be referenced as A2 and B2, A3
and B3, A4 and B4 etc., down the two columns.

Regards,
Sam

 

[Sam via OfficeKB.com]

Hi Biff,

Correction to my previous Post to you.

The Dynamic Range "Numbers" is the main source. However, I've had to set up
a summary sheet where the consecutive numbers I'm looking for are in two
separate Columns on the same Row. Eg: 72 AND 73 in Columns A and B Row 2
respectively. However, I also have other paired numbers going all the way
down column A and B Row3, Row4, Row5 etc. where I need the same Row Count
information.

Can your formula using the SEARCH Function accommodate my summary sheet
setup.

Regards,
Sam

 

[Max]

... for a complete column of paired

numbers rather than just a single Row.
The paired numbers are housed on a summary sheet
in Columns A and B starting from Row 2.

Was afraid you'd say that <g> Ok, we could try this revised set-up which
uses 3, 2 variable data tables to compute the last row number, the 2nd last
row number and the difference between the last and 2nd last row number for
the paired values in Sheet2, cols A and B, in A2:B2 down

There's no change to the set-up in Sheet1 with the formula in K20:K480

In Sheet2
---------
Put in C2 (revised slightly):

=IF(OR($A2="",$B2="",$A2=$B2),"",IF(ISERROR(LARGE(Sheet1!$K$2:$K$480,COLUMNS
($A$1:A1))),"",LARGE(Sheet1!$K$2:$K$480,COLUMNS($A$1:A1))))

Copy C2 across to D2

Put in E2: =IF(OR(C2="",D2=""),"",(C2-D2)-1)
(no change)

The above 3 formulas in C2:E2 will be utilized in setting-up 3, 2 variable
data tables, the set-ups of which are described below, The 3 data tables are
identical in structure, except for the link formula in the top left corner
cell which will point to C2, D2 and E2. I chose to use the numbers 50-81
which appear to be the range of numbers within the source table in Sheet1
for listing the horizontal "x" and the vertical "y" values in the 3 data
tables (Adapt the set up accordingly to suit your actual case)

Data Table #1
-------------
Put in G1: =C2

Number across in H1:AM1, the numbers: 50, 51, 52, 53 ... 81 (horiz x
values)
Number down in G2:G33, the numbers: 50, 51, 52, 53 ... 81 (vertical y
values)

Select G1:AM33
Click Data > Table
Enter in the boxes
For Row input cell: A2
For Col input cell: B2
Click OK

The grid H2:AM33 will compute the last row numbers at the x and y
intersections

Data Table #2
-------------
Put in G35: =D2

Number across in H35:AM35, the numbers: 50, 51, 52, 53 ... 81 (horiz x
values)
Number down in G36:G67, the numbers: 50, 51, 52, 53 ... 81 (vertical y
values)

Select G35:AM67
Click Data > Table
Enter in the boxes
For Row input cell: A2
For Col input cell: B2
Click OK

The grid H36:AM67 will compute the 2nd last row numbers at the x and y
intersections

Data Table #3
-------------
Put in G69: =E2

Number across in H69:AM69, the numbers: 50, 51, 52, 53 ... 81 (horiz x
values)
Number down in G70:G101, the numbers: 50, 51, 52, 53 ... 81 (vertical y
values)

Select G69:AM101
Click Data > Table
Enter in the boxes
For Row input cell: A2
For Col input cell: B2
Click OK

The grid H70:AM101 will compute the difference between the last and the 2nd
last row numbers at the x and y intersections

And with the 3 data tables above in place, to wrap up, we'll just need to

Put in C3:
=OFFSET($G$1,MATCH(A3,$G$2:$G$33,0),MATCH(B3,$H$1:$AM$1,0))

Put in D3:
=OFFSET($G$35,MATCH(A3,$G$36:$G$67,0),MATCH(B3,$H$35:$AM$35,0))

Put in E3:
=OFFSET($G$69,MATCH(A3,$G$70:$G$101,0),MATCH(B3,$H$69:$AM$69,0))

Then select C3:E3, and fill down as needed

Cols C to E will return (if found) the corresponding values of the last row
number, the 2nd last row number and the difference between the last and 2nd
last row numbers for the paired values entered in cols A and B

Adapt to suit ..

Note: You might want to set the calc mode to "Automatic except tables"
Click Tools > Options > Calculation tab > Check "Automatic except tables" >
OK
(Remember to click F9 to recalc the data tables if ncess., e.g., if you
redo/change the x and y values, etc)

 

[Biff]

Hi!

... for a complete column of paired
numbers rather than just a single Row.
The paired numbers are housed on a summary sheet
in Columns A and B starting from Row 2.

And Max responded:

Was afraid you'd say that <g>

Biff says:

Sam must be a pitcher on a baseball team. He's got a mean
curve ball!

Here's another solution!

On the sheet where the number table is:

In K20 enter this formula and copy down:

=A20&B20&C20&D20&E20&F20&G20&H20&I20

In L20 enter this formula and copy down:

=ROW()

On the Summary sheet:

In C2 enter this formula and copy down:

=A2&B2

Now, calculate the the number of rows between the last
instance and the next to last instance.

In D2 enter this formula with the key combo of
CTRL,SHIFT,ENTER:

=INDEX(Sheet1!L$20:L$27,LARGE(IF(ISNUMBER(SEARCH(C2,Sheet1!
K$20:K$27)),ROW($1:$8)),1))-INDEX(Sheet1!L$20:L$27,LARGE(IF
(ISNUMBER(SEARCH(C2,Sheet1!K$20:K$27)),ROW($1:$8)),2))-1

Copy down as needed.

Note: In the formula, ROW($1:$8) refers to the range size
in rows. You'll need to tweak all the references to suit.

Biff

-----Original Message-----

Was afraid you'd say that <g> Ok, we could try this revised set-up which
uses 3, 2 variable data tables to compute the last row number, the 2nd last
row number and the difference between the last and 2nd last row number for
the paired values in Sheet2, cols A and B, in A2:B2 down

There's no change to the set-up in Sheet1 with the formula in K20:K480

In Sheet2
---------
Put in C2 (revised slightly):

=IF(OR($A2="",$B2="",$A2=$B2),"",IF(ISERROR(LARGE(Sheet1! $K$2:$K$480,COLUMNS
($A$1:A1))))

Copy C2 across to D2

Put in E2: =IF(OR(C2="",D2=""),"",(C2-D2)-1)
(no change)

The above 3 formulas in C2:E2 will be utilized in setting- up 3, 2 variable
data tables, the set-ups of which are described below, The 3 data tables are
identical in structure, except for the link formula in the top left corner
cell which will point to C2, D2 and E2. I chose to use the numbers 50-81
which appear to be the range of numbers within the source table in Sheet1
for listing the horizontal "x" and the vertical "y" values in the 3 data
tables (Adapt the set up accordingly to suit your actual case)

Data Table #1
-------------
Put in G1: =C2

Number across in H1:AM1, the numbers: 50, 51, 52, 53 ... 81 (horiz x
values)
Number down in G2:G33, the numbers: 50, 51, 52, 53 ... 81 (vertical y
values)

Select G1:AM33
Click Data > Table
Enter in the boxes
For Row input cell: A2
For Col input cell: B2
Click OK

The grid H2:AM33 will compute the last row numbers at the x and y
intersections

Data Table #2
-------------
Put in G35: =D2

Number across in H35:AM35, the numbers: 50, 51, 52, 53 ... 81 (horiz x
values)
Number down in G36:G67, the numbers: 50, 51, 52, 53 ... 81 (vertical y
values)

Select G35:AM67
Click Data > Table
Enter in the boxes
For Row input cell: A2
For Col input cell: B2
Click OK

The grid H36:AM67 will compute the 2nd last row numbers at the x and y
intersections

Data Table #3
-------------
Put in G69: =E2

Number across in H69:AM69, the numbers: 50, 51, 52, 53 ... 81 (horiz x
values)
Number down in G70:G101, the numbers: 50, 51, 52, 53 ... 81 (vertical y
values)

Select G69:AM101
Click Data > Table
Enter in the boxes
For Row input cell: A2
For Col input cell: B2
Click OK

The grid H70:AM101 will compute the difference between the last and the 2nd
last row numbers at the x and y intersections

And with the 3 data tables above in place, to wrap up, we'll just need to

Put in C3:
=OFFSET($G$1,MATCH(A3,$G$2:$G$33,0),MATCH (B3,$H$1:$AM$1,0))

Put in D3:
=OFFSET($G$35,MATCH(A3,$G$36:$G$67,0),MATCH (B3,$H$35:$AM$35,0))

Put in E3:
=OFFSET($G$69,MATCH(A3,$G$70:$G$101,0),MATCH (B3,$H$69:$AM$69,0))

Then select C3:E3, and fill down as needed

Cols C to E will return (if found) the corresponding values of the last row
number, the 2nd last row number and the difference between the last and 2nd
last row numbers for the paired values entered in cols A and B

Adapt to suit ..

Note: You might want to set the calc mode to "Automatic except tables"
Click Tools > Options > Calculation tab >

Check "Automatic except tables" >

 

[Sam via OfficeKB.com]

Hi Biff,

Thank you all for ongoing help.

What's this symbol in front of the &B2 and what does it do -

?&B2?

Regards,
Sam

 

[Max]

What's this symbol in front of the &B2 and what does it do -

?&B2?

Just a few words on Biff's behalf <g>, but do hang around awhile for his
response

Using the ampersand "&" is equivalent to using the function CONCATENATE,
except that it's much shorter to type <g>

Example: Putting in C2: = A2&" "&B2
will return "Buck Rogers"
if A2 contains: "Buck", B2 contains: "Rogers"

 

[Max]

Sam,

Were you able to get the suggested set-up working ?
Or did you give up halfway <g> ?

If you're interested, I could send you a working sample book via private
email. Just post a "readable" email add. in response here.

 

[Sam via OfficeKB.com]

Hi Max,

I did get a bit lost - would appreciate working sample.

How do I disguise my email address to avoid spammers?

Regards,
Sam

 

[Sam via OfficeKB.com]

Hi Max,

Sorry to prolong the agony but in Biff's last posting there is a character
in front of the &B2 - it looks like the letter c with a line through it. Is
it an abbreviation or something?

In C2 enter this formula and copy down:
?&B2?

In K20 enter this formula and copy down:
?0&B20&C20&D20&E20&F20&G20&H20&I20?

Regards
Sam