Finding min/max values in column where cell matches something
Solution 1
Preface: I'll assume you're familiar with the Calc way of referring to Cells, which is ColumnLetterRowNumber
and ranges are expressed like this: A10:C12
mean all cells between A10 and C12, so 3 rows and three columns
Ok, I assume your data is in A1:B100
(or however many rows you have).
Now, in D1
enter Project#, in E1
enter Start date and in F1
enter End date and then list all project numbers (manually) like this:
Date Project# Project# Start date End date 2012-01-01 130 130 2012-01-02 153 Project# Start date End date 2012-01-03 153 153 2012-01-04 130 2012-01-05 130 2012-01-06 130
Now the formula for E2
is =DMIN(A1:B100,1,D1:D2)
and for F2
it's =DMAX(A1:B100,1,D1:D2)
. Then, copy cells E2:F2 to the other lines.
Unfortunately, this is the way these operations work.
Solution 2
I would use Data Pilot to create Pivot Table.
- Menu Data
- Select Data Pilot
- Click Start
- Click OK
- Drag and drop Projects to Row Fields
- Drag and drop Date to Data Fields
- Double-click on Date you've just added
- Select function Max
- Click OK
Is that what you're looking for?
Related videos on Youtube
Simon Lundberg
Updated on September 18, 2022Comments
-
Simon Lundberg about 1 year
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!
-
Simon Lundberg about 11 yearsNot quite. That just creates a one-off thing, which isn't what I need. It needs to be live and update as I enter more information.
-
Simon Lundberg about 11 yearsHow 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
-
Stefan Seidel about 11 yearsYeah, I forgot the database operations are sometimes silly. I've updated my answer.
-
kurp about 11 yearsWell, Data Pilot output is refreshed every time you're opening a file or it could be also refreshed manually. For some purposes it might be enough (or even better than resource-consuming permanent auto-refreshing). Perhaps it's possible, but I have no idea for any other simple solution (except macro, of course, but it'll not be simple any more)...
-
Simon Lundberg about 11 yearsHmm, data pilot might actually work. I didn't realize you could update it. I'll have to have another look at it. Thanks!
-
Simon Lundberg about 11 yearsChrist 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?