If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
Kludgy, but it'll work:
=MAX(IF(findnum1*findnum20,ROW(Numbers)))-LARGE(IF (findnum1*findnum20,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
|
|||
|
|||
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 atyahoodotcom ---- "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
|
|||
|
|||
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*findnum20,ROW(Numbers)))-LARGE(IF (findnum1*findnum20,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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 atyahoodotcom ---- |
#9
|
|||
|
|||
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
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Count and Sum Total occurrances of two specific numbers | Sam via OfficeKB.com | Worksheet Functions | 10 | March 29th, 2005 08:13 PM |
count a group of numbers but do not count duplicates | Lisaml | Worksheet Functions | 2 | January 26th, 2005 11:19 PM |
How do I count a range of numbers in a column | SLB | General Discussion | 3 | October 21st, 2004 05:23 PM |
Count incidences of part numbers | jmdaniel | Worksheet Functions | 13 | March 9th, 2004 05:46 PM |