Google Spreadsheet Post #1972
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Jul-19-2015
post by Christopher Krier:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/EhetRDTQwR0
Getting latest value for specified ID by date stamps
I am trying to figure out a way to filter out data from a log to grab the latest value by ID with the latest date.
Trying various methods, I got close but not quite perfect solutions that were used on other answers.
I am also looking for a generic answer, not an exact one designed per sheet. I am trying to find the logic behind this solution.
Basic Example
Column Data
A = Date Stamps
B = ID
C = Value
Example but not valid syntax. (For logic Purpose)
Using a QUERY(A:C, "select B,C where A=(MAX(A))") would result in the latest date being used showing all ID's and Values.
However the flaw in that (If that was valid syntax) would be that if there is some values that don't share the latest date but are slightly older. They will be filtered out completely.
What is wanted,I am looking for retrieving all Values for each ID of the ID's latest date stamp.
If possible, I would like an example using both QUERY and other functions, unless it is not possible with QUERY.
If possible, I would like it to be possible to include other columns of data but matching the same rules.
Rules1. ID's are only shown once with their value.
2. Return value reflects the latest entry of specified ID.
Complete ExampleGoogle Sheet
-----------------------------------------------------------------------------------------------
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/EhetRDTQwR0
Getting latest value for specified ID by date stamps
I am trying to figure out a way to filter out data from a log to grab the latest value by ID with the latest date.
Trying various methods, I got close but not quite perfect solutions that were used on other answers.
I am also looking for a generic answer, not an exact one designed per sheet. I am trying to find the logic behind this solution.
Basic Example
Column Data
A = Date Stamps
B = ID
C = Value
Example but not valid syntax. (For logic Purpose)
Using a QUERY(A:C, "select B,C where A=(MAX(A))") would result in the latest date being used showing all ID's and Values.
However the flaw in that (If that was valid syntax) would be that if there is some values that don't share the latest date but are slightly older. They will be filtered out completely.
What is wanted,I am looking for retrieving all Values for each ID of the ID's latest date stamp.
If possible, I would like an example using both QUERY and other functions, unless it is not possible with QUERY.
If possible, I would like it to be possible to include other columns of data but matching the same rules.
Rules1. ID's are only shown once with their value.
2. Return value reflects the latest entry of specified ID.
Latest being defined as that entries time stamp column being the latest date.
Complete ExampleGoogle Sheet
-----------------------------------------------------------------------------------------------
No comments:
Post a Comment