Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default Match Last Occurrence of two numbers and Return Date

Hi All,

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

I've received assistance with a similar scenario that was based on Counting
and Summing the relevant occurances of two specific numbers.

This time, I need to find /match two specific numbers that appear together
and return the Date of their LAST occurrence 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. The Date is contained in a single
Column (J) - Dynamic Range called "Date".

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

Column J = Dynamic Range "Date"
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:
Find the LAST occurrence of 72 AND 73 together and return the Date.

Regards,
Sam

--
Message posted via http://www.officekb.com
  #2   Report Post  
Domenic
 
Posts: n/a
Default

Try...

=INDEX(Date,MATCH(2,1/(COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Number
s)),0,1),72)*COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),7
3))))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
"Sam via OfficeKB.com" wrote:

Hi All,

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

I've received assistance with a similar scenario that was based on Counting
and Summing the relevant occurances of two specific numbers.

This time, I need to find /match two specific numbers that appear together
and return the Date of their LAST occurrence 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. The Date is contained in a single
Column (J) - Dynamic Range called "Date".

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

Column J = Dynamic Range "Date"
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:
Find the LAST occurrence of 72 AND 73 together and return the Date.

Regards,
Sam

  #3   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default

Hi Domenic,

Thank you very much for your time and assistance. Your formula does return
the desired result.

Thanks,
Sam

PS. Is there a book that you could recommend to assist with learning to put
together various Function syntax's as in your working formula - appeciated.

Sam

--
Message posted via http://www.officekb.com
  #4   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default

Hi Domenic,

If you can spare the time, would you mind explaining what each part of the
formula is doing.

Your Formula:
=INDEX(Date,MATCH(2,1/(COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers))
,0,1),72)*COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),73))))

....confirmed with CONTROL+SHIFT+ENTER.

Thanks again for your help.
Sam

--
Message posted via http://www.officekb.com
  #5   Report Post  
Domenic
 
Posts: n/a
Default

Let's assume that A1:J5 contains the following table...

51 58 59 65 69 72 73 76 79 Jan-05
50 51 58 72 73 76 79 80 81 Feb-05
50 52 60 62 68 69 70 75 76 Mar-05
53 54 59 60 62 69 70 72 75 Apr-05
50 51 58 59 70 71 72 73 76 May-05

....and that A1:I5 is defined as 'Numbers', and J1:J5 is defined as Date,
the following...

=INDEX(Date,MATCH(2,1/(COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Number
s)),0,1),72)*COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),7
3))))

....can be broken down as follows...

ROW(Numbers)-MIN(ROW(Numbers)) returns the following array of values...

{0;1;2;3;4}

This array of numbers is used as the second argument of the OFFSET
function. Therefore...

OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1) returns the following
array of ranges...

A1:I1
A2:I2
A3:I3
A4:I4
A5:I5

This array of ranges, in turn, is used by both COUNTIF functions...

COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),72) returns
the following array of values...

{1;1;0;1;1}

COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),73) returns
the following array of values...

{1;1;0;0;1}

When we combine these two COUNTIF functions...

(COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),72)*COUNTIF(O
FFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),73)) returns the
following array of values...

{1;1;0;0;1}

1/(COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),72)*COUNTIF
(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),73)) returns the
following array of values...

{1;1;#DIV/0!;#DIV/0!;1}

MATCH(2,1/(COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),72)
*COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),73)))
returns 5 and is used as an argument for the INDEX function to return
'May-05'.

Note that 1 divided by a number greater than or equal to 0 will always
equal a number less than or equal to 1, except where you divide by 0, in
which case you get #DIV/0!. So when you have...

MATCH(2,1/(COUNTIF(...)*COUNTIF(...)))

....MATCH ignores the #DIV/0! error values in the array of values
returned by 1/(COUNTIF(...)*COUNTIF(...)) and returns the position of
the last numerical value in that array.

Hope this helps!

In article ,
"Sam via OfficeKB.com" wrote:

Hi Domenic,

If you can spare the time, would you mind explaining what each part of the
formula is doing.

Your Formula:
=INDEX(Date,MATCH(2,1/(COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers))
,0,1),72)*COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),73))))

...confirmed with CONTROL+SHIFT+ENTER.

Thanks again for your help.
Sam



  #6   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default

Hi Domenic,

Thank you very much for your very detailed and extremely helpful
explanation of your formula. Your time and help is very much appreciated.

=INDEX(Date,MATCH(2,1/(COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers))
,0,1),72)*COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),73))))

....confirmed with CONTROL+SHIFT+ENTER.


Thanks
Sam

--
Message posted via http://www.officekb.com
  #7   Report Post  
Daniel.M
 
Posts: n/a
Default

Hi,

Also, with your data in A1:I8:
=INDEX(Dates,MATCH(2,1/MMULT((A1:H8=72)*(B1:I8=73),{1;1;1;1;1;1;1;1})))

For dynamic ranges, you can have a 7 column-wide (instead of 7) dynamic area
named : Num7
and a second one named Num7Right
=OFFSET(Num7,0,1)

Then, the formula becomes:
=INDEX(Dates,MATCH(2,1/MMULT((Num7=72)*(Num7Right=73),{1;1;1;1;1;1;1;1})) )

Regards,

Daniel M.

"Sam via OfficeKB.com" wrote in message
...
Hi All,

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

I've received assistance with a similar scenario that was based on Counting
and Summing the relevant occurances of two specific numbers.

This time, I need to find /match two specific numbers that appear together
and return the Date of their LAST occurrence 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. The Date is contained in a single
Column (J) - Dynamic Range called "Date".

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

Column J = Dynamic Range "Date"
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:
Find the LAST occurrence of 72 AND 73 together and return the Date.

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
match Ken New Users to Excel 2 March 18th 05 03:50 AM
Return Multiple Results with Lookup Josh O. Excel Worksheet Functions 1 February 4th 05 09:07 PM
Match text to another worksheet and return a certain value Edye Excel Worksheet Functions 4 December 19th 04 05:53 PM
GET NON-ZERO RESULTS USING STDEV FOR CERTAIN NUMBERS Non-zero return for Stdev Excel Worksheet Functions 2 December 16th 04 09:44 AM
Paste rows of numbers from Word into single Excel cell BecG Excel Discussion (Misc queries) 1 December 8th 04 05:55 PM


All times are GMT +1. The time now is 08:58 AM.

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

About Us

"It's about Microsoft Excel"