4
0
I have a spreadsheet laid out kind of like this:
Date Project#
2012-01-01 130
2012-01-02 153
2012-01-03 153
2012-01-04 130
2012-01-05 130
2012-01-06 130
And I want to get the min/max dates for those rows where Project# matches a variable, to produce this:
Projects Start date End date
130 2012-01-01 2012-01-06
153 2012-01-02 2012-01-03
I figured I could do it via VLOOKUP and searching in both directions, but I can't get it working properly. It all works fine as long as the project numbers are all grouped together, but if they're interleaved as in the example it does not work anymore. It only returns from the top-most grouping, so I'd get project 130's end date as 2012-01-01 instead.
I'm a bit of a spreadsheet newbie, so any help would be greatly appreciated.
Thanks!
How do I get it to use ONLY the Project# on the same row, though? I can't quite get this working either, because in E3 and F3 I need to expand the search criteria, and when I set the search criteria to E1:E3, it will include Project#=1 as well, even though I'm trying to find out about Project#=2 – Simon Lundberg – 2012-09-14T13:58:55.520
Yeah, I forgot the database operations are sometimes silly. I've updated my answer. – Stefan Seidel – 2012-09-14T14:35:08.183
Christ on a bike. This is starting to get very complicated, because my "project list" is actually filled out automatically. Getting it to fill in every other line, and then every other line with text... Ugh. :-)
Strange that there isn't just a MIN(valuearray, searcharray, criteria) where it's looking through searcharray looking for criteria, and then assembles a list from the values from valuearray where where it matches, and returns the minimum of those. That's really all I need. Is that something that's reasonably easy to implement as a custom function? – Simon Lundberg – 2012-09-14T15:34:05.173