0

:Edit -> added a working spreadsheet with the data https://docs.google.com/spreadsheets/d/1RNlZtvYZIpRF97u-7cOEGLS4my4OcLHD_1OJfDRCVCA/edit#gid=2100307022


I have a spreadsheet with a list of users, a value, and a date:

The Spreadsheet

And I would like to take the first and the last value of column B grouping by column A (the user) and shorted by column C (the date of the value) and subtract this values

John 42-106 (-64)

Arthur 47-113 (-66)

Ben 53-48 (5)

Daniel 125-125 (0)

Richard 46-121 (-75)

I'm new with the query and the most I found is a sum query, grouped correctly, but shorted by the value, not the date

=query(A:C,"Select A,B, sum(B) group by A,B")

wrong shorting

I tried to add the order by column C argument

=query(A:C,"Select A,B, sum(B) group by A,B order by C")

but it didn't work. #VALUE!

and, of course, I don't want a sum, I need a subtract

I can make a external cell with the subtract if the query only respond with the values in different columns (John | 42 | 106), but I need that the data became shorted by the date like (Ben | 53 | 48)...

What can I do?

1
  • Welcome to Web Applications. The image might be helpful to show something but it will not help to people to easily try your formulas and propose changes / alternatives, so please add the sample data as text (you can use table markdown) and consider to also include an spreadsheet with the sample data.
    – Rubén
    Apr 1, 2022 at 0:37

1 Answer 1

0

In your sample data, the first and last values are also the smallest and largest per name. If that is the case with your real data as well, you can get those values like this:

=query(A1:C, "select A, min(B), max(B) where A is not null group by A", 1)

In your sample spreadsheet, the first and last values are also the earliest and latest by the date in column C. These formulas will give results that match the expected results you show:

=query(A1:C, "select A, toDate(min(C)), toDate(max(C)) where A is not null group by A", 0)

=arrayformula( if( isnumber(K3:K) * (K3:K = J3:J), 0, iferror( vlookup($I3:$I & J3:J, { $A1:$A & int($C1:$C), $B1:$B }, 2, false) ) ) )

=arrayformula( iferror( vlookup($I3:$I & K3:K, { $A1:$A & int($C1:$C), $B1:$B }, 2, false) ) )

=arrayformula( if( M3:M + L3:L, M3:M - L3:L, iferror(1/0) ) )

8
  • Hi, thanks for your quick response, but that's the problem, not always is the case, sometimes the first value can be largest, so, there is the importance to short by date. Apr 1, 2022 at 7:33
  • Please show realistic-looking sample data together with your manually entered expected results. Provide both the sample data and the expected results as text using table markdown so that it can be copied. Also consider sharing a publicly editable sample spreadsheet. Apr 1, 2022 at 15:13
  • Thanks for your time, I'm new in this forum, I'm a little lost... here it is: docs.google.com/spreadsheets/d/… Apr 4, 2022 at 9:46
  • Edited the answer to include another solution. Apr 4, 2022 at 21:07
  • That's it! Great!. Thank you very very very much. Apr 5, 2022 at 9:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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