Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Define a range by searching for first and last?

I need a formula that searches a column of log file dates for the first and
last entry in a month and returns the address of the first and last.

Example:
A1 2-30-09
A2 2-31-09
A3 2-31-09
A4 3-3-09
A5 3-4-09
A6 3-26-09
A7 3-27-09
A8 4-2-09

If I asked it for March, it should return "A4:A7" or "A4", "A7".
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 915
Default Define a range by searching for first and last?

Gadget wrote:
I need a formula that searches a column of log file dates for the first and
last entry in a month and returns the address of the first and last.

Example:
A1 2-30-09
A2 2-31-09
A3 2-31-09
A4 3-3-09
A5 3-4-09
A6 3-26-09
A7 3-27-09
A8 4-2-09

If I asked it for March, it should return "A4:A7" or "A4", "A7".


Maybe this array* formula will help:

=ADDRESS(MATCH(3,MONTH(A1:A8),0),1)&":"&ADDRESS(MA TCH(3,MONTH(A1:A8),1),1)

Where [MATCH(3,] means March. Your list must be sorted for this to work.

*Commit array formula by pressing Ctrl+Shift+Enter, not just Enter.
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 37
Default Define a range by searching for first and last?

If the values are in A1:A8, this would return the address where the earliest
date and the latest dates are separated by a comma (but no quote marks as
you show). If you really want the quotes, it would be an easy modification.

= "A"&MATCH(MIN(A1:A8),A1:A8,0)&", "&"A"&MATCH(MAX(A1:A8),A1:A8,0)

Steve Yandl



"Gadget" wrote in message
...
I need a formula that searches a column of log file dates for the first and
last entry in a month and returns the address of the first and last.

Example:
A1 2-30-09
A2 2-31-09
A3 2-31-09
A4 3-3-09
A5 3-4-09
A6 3-26-09
A7 3-27-09
A8 4-2-09

If I asked it for March, it should return "A4:A7" or "A4", "A7".



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
Name to define a chart range mge Excel Discussion (Misc queries) 3 May 14th 09 06:31 PM
Define a range jlclyde Excel Discussion (Misc queries) 2 April 17th 08 08:26 PM
how to define range names anton New Users to Excel 1 October 14th 05 08:28 AM
Looking for code to define a range Ant Excel Discussion (Misc queries) 3 October 3rd 05 06:22 PM
Define a range based on another named range Basil Excel Worksheet Functions 2 February 21st 05 02:47 PM


All times are GMT +1. The time now is 12:52 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"