Monday, August 26, 2013

Google Spreadsheet Find most recent date in a column

Question:

( by hendis )



I'm using Google Spreadsheets on a HP laptop running Win7 through IE10. (Also have Chrome for Win 7 installed, too.)
My situation is this: 
I have a spreadsheet that lists dates sequentially in Column A from earliest to latest (starting in Cell A6). In Column B, I list clients' names opposite the date that I worked there. 
For example, cell A13 may show 9/17/11, while cell B13 might show "Arder Co." 
However, "Arder Co." will appear in Column B many times. What I want to do is find the latest date in Column A corresponding to a specific client's name.
For example, if "Arder Co." appears in cells B13, B37, B48, B53, and B66, I want to return the most recent date, which will appear opposite to cell B66, or in cell A66.
This is easier to explain than to do. Thanks in advance for any ideas or help.

Solution:

Have a look at the following screenshot:




I have the following formula in Cell D5:
=query(A5:B;"select B,max(A) where B<>'' group by B label max(A) 'Date' ";1)


I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.


I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

5 comments:

  1. Very helpful indeed! Thanks much!

    ReplyDelete
  2. Thank you! This is exactly what I wanted.

    May I be greedy and ask you how I would include additional columns? I'd like to include two columns from the range in addition to the date but I don't understand the syntax well enough to do so.

    ReplyDelete
  3. I'm using Google sheets and trying to find a prior service date for each location. I'd like to have a result look like the below. I already have data filled in for column A and B.

    What formula should I use for column C?



    COLUMN A ----- COLUMN B ----- COLUMN C

    DATE ------------- LOCATION ----- PRIOR SERVICE DATE

    10/21/2015 ----- Bar & Grill -----

    11/12/2015 ----- Bar & Grill ----- 10/21/2015

    11/24/2015 ----- Bar & Grill ----- 11/12/2015

    7/16/2015 ----- Asian Restaurant -----

    7/31/2015 ----- Asian Restaurant ----- 7/16/2015

    8/12/2015 ----- Asian Restaurant ----- 7/31/2015

    7/16/2015 ----- Bistro -----

    7/31/2015 ----- Bistro ----- 7/16/2015

    8/12/2015 ----- Bistro ----- 7/31/2015

    10/28/2015 ----- Drive In -----

    11/9/2015 ----- Drive In ----- 10/28/2015

    11/24/2015 ----- Drive In ----- 11/9/2015

    Thanks in advance for your help.

    ReplyDelete
  4. any solution for this ?
    https://productforums.google.com/forum/?utm_medium=email&utm_source=footer#!msg/docs/TDWrsgz34O8/TuOONuhCLwAJ

    ReplyDelete