4

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!

4 Answers 4

4

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.

3
  • 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 Sep 14, 2012 at 13:58
  • Yeah, I forgot the database operations are sometimes silly. I've updated my answer. Sep 14, 2012 at 14:35
  • 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? Sep 14, 2012 at 15:34
1

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?

3
  • 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. Sep 14, 2012 at 13:02
  • 1
    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)...
    – kurp
    Sep 14, 2012 at 14:39
  • Hmm, data pilot might actually work. I didn't realize you could update it. I'll have to have another look at it. Thanks! Sep 14, 2012 at 15:32
0

I know I looked at this in September, but I took a fresh look at it today and came up with a new wrinkle.  It’s based on Stefan’s answer, but it addresses the problem of database “criteria” requiring two rows by having two columns of them and alternating between them:

    enter image description here

Here are the formulas:

  enter image description here

Please let me know if this is not clear.

0

Scott, thanks for your refinement - it really helped. Just to simplify the setting up of the spreadsheet I took the following steps:

1 - duplicate the column of data I want to find the max for and add it to the bottom (so there are twice as many rows as there is data)

2 - in the column next to the duplicated data (C in your example) put 1 in the top cell and then =IF(ISODD(ROW(A2));C1+1;C1) copied all the way to the bottom of the data (step 1 just makes it easy to find the bottom)

3 - in the next column put =IF(ISODD(ROW(A1));"Project";OFFSET($B$1;C1;0)) and again copy to the end of the duplicated data - this produces the alternated field names and values we want to interrogate

4 - the maximum is then found by =DMAX($A$1:$B$3437;"Livedate";OFFSET(D$1;ROW($A1)*2-2;0;2;1)), which gives the maximum in each row as in your example.

I have a lot of data - 3400 rows - so doing it this way just made it easier to build the sheet. Thanks for the hint, hope this may be useful to others.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .