Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 2nd 05, 12:01 AM
Sam via OfficeKB.com
 
Posts: n/a
Default Match Last Occurrence of two numbers and Count to Previous Occurence

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

  #2   Report Post  
Old April 2nd 05, 03:19 AM
Biff
 
Posts: n/a
Default

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

-----Original 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
.

  #3   Report Post  
Old April 2nd 05, 03:49 AM
Jason Morin
 
Posts: n/a
Default

Kludgy, but it'll work:

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

Array-entered, whe

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

-----Original 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
.

  #4   Report Post  
Old April 2nd 05, 04:06 AM
Max
 
Posts: n/a
Default

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



  #5   Report Post  
Old April 2nd 05, 04:16 AM
Biff
 
Posts: n/a
Default

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

Biff

-----Original Message-----
Kludgy, but it'll work:

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

Array-entered, whe

findnum1 =

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

findnum2 =

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

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

HTH
Jason
Atlanta, GA

-----Original 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
.

.



  #6   Report Post  
Old April 2nd 05, 04:26 AM
Sam via OfficeKB.com
 
Posts: n/a
Default

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

--
Message posted via http://www.officekb.com
  #7   Report Post  
Old April 2nd 05, 04:46 AM
Biff
 
Posts: n/a
Default

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

-----Original Message-----
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

--
Message posted via http://www.officekb.com
.

  #8   Report Post  
Old April 2nd 05, 05:32 AM
Max
 
Posts: n/a
Default

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)
--
Rgds
Max
xl 97
---
GMT+8, 1 22' N 103 45' E
xdemechanik <atyahoo<dotcom
----


  #9   Report Post  
Old April 2nd 05, 05:46 AM
Sam via OfficeKB.com
 
Posts: n/a
Default

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

--
Message posted via http://www.officekb.com
  #10   Report Post  
Old April 2nd 05, 06:01 AM
Sam via OfficeKB.com
 
Posts: n/a
Default

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

--
Message posted via http://www.officekb.com


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count and Sum Total occurrances of two specific numbers Sam via OfficeKB.com Excel Worksheet Functions 10 March 29th 05 08:13 PM
Count 350 SS numbers, exclude duplicates Marsha Excel Discussion (Misc queries) 5 March 7th 05 05:49 PM
count cells with unique numbers Alex Excel Worksheet Functions 1 February 21st 05 07:46 PM
Count Consecutive Numbers in a Row Sam via OfficeKB.com Excel Worksheet Functions 5 February 19th 05 02:49 AM
count a group of numbers but do not count duplicates Lisaml Excel Worksheet Functions 2 January 26th 05 11:19 PM


All times are GMT +1. The time now is 04:42 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017