2

Want to get the results described on the screenshot. How can I achieve it.

enter image description here

QUERY:

=QUERY(A2:F12,"SELECT A, MAX(B), MIN(B) group by A label A 'G V1', MAX(B) 'LAST V2', MIN(B) 'FIRST V2'")

Want to get First & last V5 value in terms of max and min dates in V2 (see K2, L2 desired results), I could get the last and first dates now I want their V5 corresponding.

  • 1
    Welcome. Please read how to and share a test sheet so as you can be easier helped. – marikamitsos Jun 1 at 2:15
  • @marikamitsos Find the shared sheet: docs.google.com/spreadsheets/d/… – Mostav Jun 2 at 19:19
  • @marikamitsos, any feedback or hint? thank you – Mostav Jun 3 at 13:40
  • Welcome to Web Applications. Please add a brief description of your search/research regarding the result you are looking for as is suggested in How to Ask. – Rubén Jun 4 at 23:12
  • I worthy to note that the result you are looking could not be achieved by using a single QUERY function, in other words, you should use another formula or use a single but complex formula. If the values in B are unique you could use them as "keys" and use VLOOKUP, INDEX and MATCH or other similar lookup soluctions. – Rubén Jun 4 at 23:15
0

I'm not sure if this will work for the entire end goal, but, based on the observation that every column is the result of a query returning one row, is it possible that you may be able to have one simple query per column, each returning one row. For example: enter image description here

The formula for columns:

  • E1: =query(Sheet1!$A$2:$A$8, "select max(A) group by A")
  • F1: =query(Sheet1!$A$2:$B$8, "select max(B) group by A")
  • G1: =query(Sheet1!$A$2:$C$8, "select min(B) group by A")
  • H1: =query(Sheet1!$A$2:$C$8, "select max(C) group by A")
  • I1: =query(Sheet1!$A$2:$C$8, "select min(C) group by A")

Notes:

  • google sheets wants to stick in the "max" and "min" title & it's a pain to remove it
  • you can remove the Sheet1, that's just there in case the data is on a different sheet (tab). Of course change it or remove it (and the trailing !) if the sheet isn't called Sheet1.
  • if it's easier to include the entire range of the whole table, it'll still work, but might impact performance(?), e.g., you can use $A$2:$C$8 in each query.
| improve this answer | |
0

You can even have your results in a single long formula in cell H1

={QUERY({A2:F},"SELECT Col1, MAX(Col2), MIN(Col2) where Col1 is not null group by Col1 label Col1 'G V1', MAX(Col2) 'LAST V2', MIN(Col2) 'FIRST V2' "), 
   QUERY({"LAST V5", "FIRST V5";IFERROR(ArrayFormula(VLOOKUP(QUERY({A2:F},"SELECT MAX(Col2), MIN(Col2) where Col1 is not null group by Col1 label MAX(Col2) 'LAST V2', MIN(Col2) 'FIRST V2' "),B2:F,{4,4},0)))})}

(I would personally go against it since you mention: "Actually, i am not expert in google sheets...")


Please use the following formula in cell K2

=IFERROR(ArrayFormula(VLOOKUP(I2:J,B2:E,{4,4},0)))

enter image description here

In the formula we used open ranges. That means that the ranges are in the form I2:I, B2:E etc.

The same way you can extend your original query formula to the open range A2:F

=QUERY(A2:F,"SELECT A, MAX(B), MIN(B) where A is not null group by A label A 'G V1', MAX(B) 'LAST V2', MIN(B) 'FIRST V2'")

Notice that we added the where clause where A is not null to omit empty cells.

Functions used:

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.