Find earliest date based on certain criteria

Ok so let me set this up. I have 2 columns:

Project Date Start
NY 1/14/2009
Cali 5/6/2010
NY 7/2/2009
NY 8/2/2010
Cali 3/2/2009
Cali 10/2/2011

Ok, so what function can I use that will output the earliest "Date Start" but using the criteria of "Project". I want to make sure that when this list grows to 1,000+ that I don't have to go back through and create a new formula each time. The objective is to have Tab 1 show:

Project Date Start
California 3/2/2009
New York 1/14/2009

So on and so forth for new projects as they come on.

Things I have tried and might just be scripting wrong (Keep in mind that for me "Project" is column B and "Date Start" is column E:

=IF((IF(T(B:B)="cali",COUNT(E:E),"")),MIN(E:E),"")

I have found several ways to pull the lowest date, but not when I need it to reference it based on the criteria of "Project". Anything you can offer would be great! Thanks!

Simplest thing would be to use a Pivot Table - use a Dynamic Named Range as source (see link in sig. for more info) ... set Project as Row Field / Label and Start Date as Data Field set to MIN format as date.

Yes but unless the source data is first sorted it will be more expensive long term...

If your data resides on Sheet2 and your "final" project listing on Sheet1 then with first project name listed in say A2:

Please Login or Register  to view this content.

that in red is imperative... also note Arrays are inefficient and keeping ranges "lean" is a must for the sake of overall workbook performance.

Of course were your source data (sheet2) sorted first by project and latterly by date (asc) you could then dispense with the Array and revert to far more basic and efficient INDEX/MATCH function.

I would still strongly advise the PT route... I suspect you may find you get a lot of unexpected gains from this approach in the long run (ie reporting power for no effort)

Originally Posted by SK

I'm trying that formula presented and it's coming back being flagged that it ran out of resources (I'm assuming this is why the PT is better).

It shouldn't ... did you change the references such that you are using entire column references ?
If so, as advised, don't - in XL2007 this means you're processing over 2 million cells per calculation (!) ... pre XL2007 this would generate #NUM! errors (ie not permitted)

You say the data is sorted by Project, is it sorted by Date also ?

Regards expanding ranges - note my point in post # 3 re: Dynamic Named Ranges.

Ok, so now, I have created my dynamic name ranged:

=OFFSET(Value!$B$1,0,0,COUNTA(Value!$B$1:$B$200),1)

This is showing what I want, where there is information, however, it is still going down the column showing #value! where there is no information for it to pull yet. Did I type it in wrong?

*** I just changed COUNTA to MATCH, and changed the end accordingly, but still getting the same issue

I confess I'm struggling to visualise exactly...

You would potentially get a #VALUE! error with the MIN formula if you did not confirm as an Array (ie see text in red) - ie you must use CTRL + SHIFT + ENTER ... that said have you created a Named range for Column E also ?

Here would be my suggestion... without knowing remaining particulars of your file...

If we assume that Project column is never empty when the row is in use (ie no interspersed blanks - as implied by your earlier use of COUNTA) then:

Please Login or Register  to view this content.

You can then either

a) create a Pivot Table and use _DATA as reference (create accordingly)
(assumes you have header values in row 1 on Value sheet in Columns B:E)

b) use the earlier array but adapt per the above name range, eg:

Please Login or Register  to view this content.

becomes

Please Login or Register  to view this content.

In the above you will note I used INDEX & MATCH to create the Dynamic Name rather than OFFSET - this is purely because OFFSET is Volatile and INDEX is not thus I prefer the INDEX approach (it's a personal thing)

Re: use of REPT("Z",255) - this is technique for finding last values of a given data type (I've assumed Project Names are always text strings (ie never numbers)) - for a guide on "finding last value" techniques see Bob Phillips' white paper: http://www.xldynamic.com/source/xld.LastValue.html

If you're still struggling it might be an idea to post a non-confidential version of your file so we can see what you're using exactly.

Have a crack working through the above - if it doesn't work all we need see in your sample file is the Value sheet and the sheet on which you're trying to perform the calcs.

If there are formulae in Column B on the Values sheet we'd need to know what the formula is - the formulae need not be in the sample file (so as to reduce linkage - number of sheets required in sample kept to a min)

In terms of # of rows etc - we're really only need to see a subset of say 1000 rows and a few projects to validate what's happening ... but to reiterated, condescending as it may sound it would be worthwhile walking through on your own in the first instance ... if you get it working it will be have been time well spent I'd say.

OK, the column references have changed slightly from before but referring solely to the MIN function...

If I alter references from E to K and alter sheet name from Sheet2 to Data and enter below into I3 as an Array

Please Login or Register  to view this content.

and copy down it generates expected results.

If the Data tab always has transactions sorted by Project (ascending order) as implied you could avoid Array if preferred and revert to a standard MIN using INDEX to determine the appropriate range against which it should be applied, eg:

Please Login or Register  to view this content.

(in all of the above we're assuming that the Project listed in B always exists on Data sheet and always has a date value assigned - ie no pre-emptive error checks are required)

Originally Posted by SK

The VA and Roanoke did not pick up (They are instead just taking the lowest overall date). And when i went back to data and added in another New York with an earlier date, I was not picked up. Thoughts?

Originally Posted by D.O

If the Data tab always has transactions sorted by Project (ascending order) as implied you could avoid Array if preferred and revert to a standard MIN using INDEX to determine the appropriate range against which it should be applied...

In the latest attachment the data is no longer sorted by Project thus the non-array MIN(INDEX based approach is not viable.

Please post a version in which you have added the suggested Array without achieving desired results
(remember CTRL + SHIFT + ENTER to commit the formula, not just Enter like "normal" formulas)