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)),ISN UMBER(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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Sam via OfficeKB.com" wrote in message
...
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
--
Message posted via http://www.officekb.com