-1

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
ZygD
  • 22,092
  • 39
  • 79
  • 102
Max R
  • 798
  • 2
  • 7
  • Can you use Power Query instead? – ALeXceL Aug 01 '22 at 01:08
  • @ALeXceL I hadn't thought of that, the worksheet will be used, and in the future maintained by an excel novice for their personal use, I'm worried that I'll bury them in complexity, but I'll look into it. – Max R Aug 01 '22 at 03:17
  • Your current example isn't clear to me. Perhaps a few more examples in tabular form would be useful. – Jos Woolley Aug 01 '22 at 03:51
  • @JosWoolley Your formula worked perfectly. And more importantly to me, it was a great use case for the SCAN function. I use dynamic arrays a lot, and have always been puzzled about what my use case for scan/reduce would ever be. Your example has opened up a door to a whole bunch of situations that I hadn't considered. Wanted to circle back and thank you for your answer. – Max R Sep 12 '22 at 04:12

1 Answers1

3

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.

Jos Woolley
  • 8,564
  • 2
  • 4
  • 9