On Wed, 20 Nov 2013 21:49:02 -0800 (PST), Nitya Satheesh wrote:

Hi!

I have a spreadsheet filled with data that includes date and time. For a particular value in column a i need to find the max and min time.

My sheet looks a little like this

Column a columnb
1234 2/01/2013
1234 3/01/2013
1234 3/01/2013
1234 4/01/2013
1234 5/01/2013
1234 6/01/2013
5678 1/01/2012
5678 2/01/2012
5678 3/01/2012
5678 4/01/2012

So I need to find the max and min dates for the values 1234 and 5678 etc,.

Please help!
Thanks!

There are several ways to do this, depending on what your real data looks like.

You can use a Pivot Table
Insert Pivot Table
Drag Col A to the Rows Area
Drag Col B to the Values area twice
Click the drop down arrow on the Col B items to get to the Value Settings Menu
Set one to Min and the Number Format to Date
Set the other to Max and the Number Format to Date


Values
Row Labels Max of Col B Min of Col B
1234 6/1/13 2/1/13
5678 4/1/12 1/1/12

If your values are really in sorted order as above, with the both columns sorted as you show, then you can do something like

F2: 1234
F3: 5678

For Max G2: =LOOKUP(2,1/(Col_A=F2),Col_B)
or you might try: =VLOOKUP(F2,Col_A:Col_B,2)
For Min H2: =VLOOKUP(F2,Col_A:Col_B,2,FALSE)