CODEWITHSUNDEEP
google-sheetsgoogle-visualizationgoogle-sheets-query
Harrison
Harrison

Reputation: 19

Using Google Sheets Query to get Max Date for rows of data

I've been trying to use Google Sheets Query to find the more recent data of a particular name while pulling the rest of the data from the row. And having not luck.

This is a sample of what the Raw Data looks like: raw data

And this is the output that I would like to achieve: Output

The closest I got to the output that I need is

=QUERY('Raw Data'!A:F,"select max(A), B, C, D, E group by B, C, D, E",1)

But the most important data is in F. When I add in F to 'select' the formula breaks, so I also grouped by F. Then that also pulls in the 4/1/17 data for Mark.

Anyone know whats going on? Or have a better formula for me to work with?

Thanks!

Upvotes: 1

Views: 17465

Answers (1)

Jeremy Kahan
Jeremy Kahan

Reputation: 3826

As far as what is going on, I worked with your formula and it picked up the extra date for Mark even before I added column F. As I understand it, if you want the max date per person, you really need =QUERY('Raw Data'!A:F,"select max(A),B group by B",1)

I put that in A1 in the worksheet I was using (not the same as the raw data one). So in its column A and B, I now had the maximum date and the person's name. Next to the first date, I put in column C the following formula, then dragged it down. I was simplifying to do just dates, but I believe it could be adapted to handle times if needed (if the person does multiple times a day and you want only the latest). ="'"&text(A3,"yyyy-mm-dd")&"'".

This gave me the date in single quotes, which I then used in column D, that built up the where clause, with the following formula in the first row,="(A=date "&C3&" and B='"&B3&"')" and the draggable formula =D3&" or "&"(A=date "&C4&" and B='"&B4&"')" in each subsequent row.

In the example with 3 people, the last clause ended up in D5 for me, so my final query was=QUERY('Raw Data'!A:F,"select A,B,C,D,E,F where"&D5,1), and that generated the desired results. My hesitation about this answer is whether it will scale, or whether it will too quickly hit a limit on length of the where clause.

Upvotes: 1

Related Questions