CODEWITHSUNDEEP
google-sheetsgoogle-docs
Ajit KS
Ajit KS

Reputation: 171

How do I group similar cells and get the maximum value among those cells?

╔══════╦════════════════════╦══════════╗
║ Name ║        Date        ║  Stage   ║
╠══════╬════════════════════╬══════════╣
║ Dave ║ 4/29/2016 12:15:36 ║ Booking  ║
║ Dave ║ 4/29/2016 17:58:24 ║ Booking  ║
║ Dave ║ 4/29/2016 11:32:59 ║ Costing  ║
║ Dave ║ 4/29/2016 16:52:47 ║ Costing  ║
║ Dave ║ 4/27/2016 16:59:27 ║ Lead     ║
║ Mark ║ 4/22/2016 12:55:22 ║ Booking  ║
║ Mark ║ 4/24/2016 8:00:45  ║ Booking  ║
║ Mark ║ 4/24/2016 10:07:01 ║ Booking  ║
║ Mark ║ 4/24/2016 14:15:30 ║ Booking  ║
║ Mark ║ 4/24/2016 18:55:22 ║ Booking  ║
║ Mark ║ 4/24/2016 21:33:12 ║ Booking  ║
║ Mark ║ 4/20/2016 12:55:22 ║ New      ║
║ Mark ║ 4/23/2016 16:32:22 ║ Planning ║
║ Mark ║ 4/24/2016 9:11:36  ║ Won      ║
║ Mark ║ 4/24/2016 12:55:22 ║ Won      ║
║ Mark ║ 4/24/2016 17:04:02 ║ Won      ║
║ Mark ║ 4/24/2016 19:55:22 ║ Won      ║
║ Mark ║ 4/25/2016 10:19:13 ║ Won      ║
╚══════╩════════════════════╩══════════╝

I have the above table on my Google Spreadsheet. What I'm trying to do is group duplicate stages and return the maximum date for each group of stage.

I know how to get the maximum date for all the cells by doing this: =max(arrayformula((B$2:B$19=B2)*(C$2:C$19)))

But, I'd like to return the maximum date of each stage. I would like my table to look like below:

╔══════╦════════════════════╦══════════╦════════════════════╗
║ Name ║        Date        ║  Stage   ║      Max Date      ║
╠══════╬════════════════════╬══════════╬════════════════════╣
║ Dave ║ 4/29/2016 12:15:36 ║ Booking  ║ 4/29/2016 17:58:24 ║
║ Dave ║ 4/29/2016 17:58:24 ║ Booking  ║ 4/29/2016 17:58:24 ║
║ Dave ║ 4/29/2016 11:32:59 ║ Costing  ║ 4/29/2016 16:52:47 ║
║ Dave ║ 4/29/2016 16:52:47 ║ Costing  ║ 4/29/2016 16:52:47 ║
║ Dave ║ 4/27/2016 16:59:27 ║ Lead     ║ 4/27/2016 16:59:27 ║
║ Mark ║ 4/22/2016 12:55:22 ║ Booking  ║ 4/24/2016 21:33:12 ║
║ Mark ║ 4/24/2016 8:00:45  ║ Booking  ║ 4/24/2016 21:33:12 ║
║ Mark ║ 4/24/2016 10:07:01 ║ Booking  ║ 4/24/2016 21:33:12 ║
║ Mark ║ 4/24/2016 14:15:30 ║ Booking  ║ 4/24/2016 21:33:12 ║
║ Mark ║ 4/24/2016 18:55:22 ║ Booking  ║ 4/24/2016 21:33:12 ║
║ Mark ║ 4/24/2016 21:33:12 ║ Booking  ║ 4/24/2016 21:33:12 ║
║ Mark ║ 4/20/2016 12:55:22 ║ New      ║ 4/20/2016 12:55:22 ║
║ Mark ║ 4/23/2016 16:32:22 ║ Planning ║ 4/23/2016 16:32:22 ║
║ Mark ║ 4/24/2016 9:11:36  ║ Won      ║ 4/25/2016 10:19:13 ║
║ Mark ║ 4/24/2016 12:55:22 ║ Won      ║ 4/25/2016 10:19:13 ║
║ Mark ║ 4/24/2016 17:04:02 ║ Won      ║ 4/25/2016 10:19:13 ║
║ Mark ║ 4/24/2016 19:55:22 ║ Won      ║ 4/25/2016 10:19:13 ║
║ Mark ║ 4/25/2016 10:19:13 ║ Won      ║ 4/25/2016 10:19:13 ║
╚══════╩════════════════════╩══════════╩════════════════════╝

Upvotes: 0

Views: 47

Answers (1)

Ed Nelson
Ed Nelson

Reputation: 10259

Try using query. Enter in D2 and copy the formula down:

=query($A2:$C19,"select B where A='" & A2 &"' and C='" & C2 &"' order by B desc limit 1 ")

Select B gets the dates. Where A = '" & A2 &"' is limiting to the value in cell A2 (the name). C2= is doing the same thing to the cell value in C2. It orders by date. In this case decending order. Limit 1 effectively gets the max date since it only returns one value. I hope that explain the query.

Upvotes: 1

Related Questions