I'm a very advanced Excel user and have used dynamic arrays and complex lambdas to solve some pretty tough problems, but this one has me stumped. I have a column (or array) of invoice dates, that are non-unique. I have a corresponding column of the count of a specific widget sold on that invoice. It could be 0, 1, or multiples. And a given day could have 3 invoices with no widgets, 2 invoices with one widget, and 1 invoice with 4 widgets.
I am not trying to get the count, that's trivial. I need to create an array that is the dates of the last 6 widget sales, including duplicated dates. So if on 7/1/22 3x widgets were on one invoice, and and on 7/5 there were 2x on 1 invoice and 1x on a second invoice, my resulting array would have 3x 7/1 entries and 3x 7/5 entries. It's a list of the date that each of the last 6 widgets were sold.
I can get it to one single intermediate table, but I can't find a way to do it using a single formula and dynamic arrays. In this application, I can't resort to VBA, which would be easier.
Adding example source data:
| Date | Ct |
|---|---|
| 6/5 | 1 |
| 6/7 | 1 |
| 6/7 | 2 |
| 6/10 | 0 |
| 6/10 | 1 |
| 6/25 | 1 |
| 6/26 | 0 |
| 6/28 | 1 |
Example result: the last 6 items ordered were ordered on what date?
| Rslt |
|---|
| 6/7 |
| 6/7 |
| 6/7 |
| 6/10 |
| 6/25 |
| 6/28 |
With Invoice Date and Widget Sales in A1:A10 and B1:B10 respectively, and C1 containing your chosen value for X, e.g. 6:
=LET(ζ,A1:B10,ω,C1,ξ,SORT(ζ,1,-1),λ,XLOOKUP(SEQUENCE(ω),SCAN(0,INDEX(ξ,,2),LAMBDA(α,β,α+β)),INDEX(ξ,,1),,1),FILTER(λ,1-ISNA(λ)))
The final FILTER clause is included so that, if less than X sales are present, dates corresponding to the actual number of sales are returned.