Forum Discussion
Excel Dynamic Array - Resetting a Row Count
Sharing a problem and solution which I believe might be helpful. I've been working for a while on coming up with a way to sort and rank a list of customer / item / other identifier fields which a...
mswartzentruber With Customers in column D and Items in column E, a couple more options could be:
=LET(a,D2:D26&" - "&E2:E26,b,a=VSTACK("",DROP(a,-1)),r,SCAN(0,b,LAMBDA(p,c,p*c+1)),a&" - "&r)
--OR--
=LET(a,D2:D26&" - "&E2:E26,n,SEQUENCE(ROWS(a)),r,MMULT((a=TOROW(a))*(n>=TOROW(n)),n^0),a&" - "&r)
It's important to note, with the first method shown above, the data must be sorted by customer (the record count is reset to 1 at every change in customer name), whereas the second method with MMULT will continue to increment the item count by customer regardless of the sort order.
See attached, if desired...