1

thanks for the help! Sorry if I'm expressing myself in a confusing manner, I'm a beginner at this... I'm trying to create a spreadsheet to help me plan and analyze grocery expenses more efficiently.

I have a tab on my spreadsheet with 4 columns: C - Product name, D - Price, E - Store, and A - Date of purchase.

So I have multiple times "Sliced bread" listed under "Product name", but each time it has a different Price and Date at a different Store.

There's only a few different stores, so they're repeated many times. And in one date I purchase many different products, so the same date is repeated many times as well.

I want to use Query to find out what has been the lowest Price I've paid for each Product, and what Date it was and at what Store.

Here is what I've tried:

=QUERY('23 Apr - 23 May'!A1:F80,"select C, MIN(D) group by C order by C", 1)

The above works, listing each Product only once alphabetically with the smallest Price, but it of course does not return the Store and Date of the purchase, which I'd like it to.

=QUERY('23 Apr - 23 May'!A1:F80,"select C, MIN(D), E, A group by C, E, A order by C", 1)

The above at least works, but it's returning the same Product repeated as many times as there are unique Dates and Stores.

=QUERY('23 Apr - 23 May'!A1:F80,"select C, MIN(D), E, A group by C order by C", 1)

This I would hope would work - Each Product only once alphabetically, showing the MIN Price, along with the Store and Date of purchase. I'm getting this error though: "Error Unable to parse query string for Function QUERY parameter 2: ADD_COL_TO_GROUP_BY_OR_AGG: E"

=QUERY('23 Apr - 23 May'!A1:F80,"select C, MIN(D), MAX(E), MAX(A) group by C order by C", 1)

This shows me mismatched columns - I'm getting the lowest Price with the most recent Date, even if that's not when the purchase was made...

  • pls share a copy of your sheet – user0 May 4 at 3:28

Your Answer

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

Browse other questions tagged or ask your own question.