Finding min/max values in column where cell matches something

12,998

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.

  1. Menu Data
  2. Select Data Pilot
  3. Click Start
  4. Click OK
  5. Drag and drop Projects to Row Fields
  6. Drag and drop Date to Data Fields
  7. Double-click on Date you've just added
  8. Select function Max
  9. Click OK

Is that what you're looking for?

Share:
12,998

Related videos on Youtube

Simon Lundberg
Author by

Simon Lundberg

Updated on September 18, 2022

Comments

  • Simon Lundberg
    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
    Simon Lundberg about 11 years
    Not 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
    Simon Lundberg about 11 years
    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
  • Stefan Seidel
    Stefan Seidel about 11 years
    Yeah, I forgot the database operations are sometimes silly. I've updated my answer.
  • kurp
    kurp about 11 years
    Well, 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
    Simon Lundberg about 11 years
    Hmm, 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
    Simon Lundberg about 11 years
    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?