Hello all,

I just can not wrap my head around this.

Imagine this:

A list of prices for 10 products on certain dates (for every date, the price
for each of the 10 products is listed).
Column A = Product
Column B = Date
Column C = Price

In some months, prices are checked only once........in other months, prices
are checked twice.

In column D, I want a formula to list the earliest date in that month that
shows up in the whole list (unsorted list preferred).

Example:

using the dates:
1/5/2007
2/4/2007
3/6/2007
3/4/2007

would return:
1/5/2007 1/5/2007
2/4/2007 2/4/2007
3/6/2007 3/4/2007
3/4/2007 3/4/2007

For those who know SQL, I would do this in SQL to get the results I'm
looking for:

SELECT Min([Date]) as MinDate
FROM [SomeTable]
GROUP BY Year([Date]), Month([Date])

Then I would use the results from this to join back to [SomeTable] to either
look up or limit my results from [SomeTable].


Thanks for any help anyone can provide,

Conan Kelly

This is an array formula that must be entered using ctrl+shift+enter. Use
only the 10
=MIN(IF(MONTH($F$2:$F$22)=ROW(A1),$F$2:$F$22))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Conan Kelly" wrote in message
...
Hello all,

I just can not wrap my head around this.

Imagine this:

A list of prices for 10 products on certain dates (for every date, the
price for each of the 10 products is listed).
Column A = Product
Column B = Date
Column C = Price

In some months, prices are checked only once........in other months,
prices are checked twice.

In column D, I want a formula to list the earliest date in that month that
shows up in the whole list (unsorted list preferred).

Example:

using the dates:
1/5/2007
2/4/2007
3/6/2007
3/4/2007

would return:
1/5/2007 1/5/2007
2/4/2007 2/4/2007
3/6/2007 3/4/2007
3/4/2007 3/4/2007

For those who know SQL, I would do this in SQL to get the results I'm
looking for:

SELECT Min([Date]) as MinDate
FROM [SomeTable]
GROUP BY Year([Date]), Month([Date])

Then I would use the results from this to join back to [SomeTable] to
either look up or limit my results from [SomeTable].


Thanks for any help anyone can provide,

Conan Kelly