1

I have a sheet with a lot of data, but to keep this simple let's say column A has a product, column B has a date, and column C has an integer which can be blank, 1, or an integer greater than 1:

Product  Date      # Sold
Thing1   2020-9-9       1
Thing2   2020-9-9       2
Thing3   2020-9-9       3
Thing1   2020-8-8       1
Thing2   2020-8-8       2
Thing3   2020-8-8       1
Thing1   2020-7-7       2

I need to find the date on which I sold the 3rd-last of that item:

Product  Turn 3 Date
Thing1   2020-7-7
Thing2   2020-8-8
Thing3   2020-9-9

Note that for other reasons I will likely end up with a Query in each cell of the second table anyway, so it doesn't need to get any more complicated than something like:

=query(Table1!A1:C,"Select min(B) where A='"&A2&"' limit X")

where "X" would be how to limit it to the running total of 3.

Thanks in advance for any ideas!

Improve this question
4
  • If you will enter the sample data from your post into an actual sheet and then share that link here, being sure to set the Share permission to "Anyone with the link can edit," you will likely receive increased engagement with your post. Without a sheet, those here must recreate your data on their own Drive. In addition, the explanations would require many more words than if people here were able to show you the solutions in action on your sheet.
    – Erik Tyler
    Oct 19, 2020 at 1:26
  • do you still need help with this? If so, please read my previous comment. If not, please let us know that you solved the issue on your own.
    – Erik Tyler
    Oct 23, 2020 at 1:38
  • Thanks for the prompt to come back with the answer, Erik! I did end up figuring it out, but it took a while. Answer below.
    – flyingcheesehead
    Oct 23, 2020 at 3:46
  • This could have been achieved with one formula (i.e., without the drag-down formulas in Column D; in fact, without Column D at all). But if the added Column D and the drag-down formulas works for you, that is all that matters. Glad you were able to find a workable solution. And figuring it out on your own, while it can be tedious, is the best way to learn—and to have the most control over your own sheet, because you understand what YOU did.
    – Erik Tyler
    Oct 23, 2020 at 17:08

1 Answer 1

Reset to default
0

I did finally solve this.

The solution involved first creating another column with cumulative sales of each product. Using the data above:

Product  Date      # Sold   Cumulative
Thing1   2020-9-9       1   1
Thing2   2020-9-9       2   2
Thing3   2020-9-9       3   3
Thing1   2020-8-8       1   2
Thing2   2020-8-8       2   4
Thing3   2020-8-8       1   4
Thing1   2020-7-7       2   3

The formula for the cumulative column looks like this:

 =sumproduct(--($A$2:$A=$A2)*--($B$2:$B>=$B2),$C$2:$C)

The -- has the effect of converting a boolean to a number. The * becomes a logical AND. The sumproduct essentially multiplies the matrices from its arguments.

So, if something in column A is the same as the thing in cell A2 ($A$2:$A=$A2) AND the date is greater than the date on this row, the first argument is a 1 and it gets multiplied by the number of things sold on that row. But, this calculation is looking at all of the other rows on each and every row, so I can imagine that large data volumes might not work well with it.

Anyway, once we have that Cumulative column, it's a pretty simple query to get the date on which the 3rd-last item was sold:

=QUERY($A$2:$D,"Select B where A='"&A2&"' and D>3 Order By B desc Limit 1")

Works like a charm so far!

Improve this answer

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.