Forum Discussion

mswartzentruber's avatar
mswartzentruber
Copper Contributor
Oct 04, 2024

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 are part of a dynamic array (orange data). The goal was create a counter beginning at 1 for each customer/item combination, which would reset when a new item combination was identified, giving me the Customer-Item Counter in the green column. I wanted to avoid the need to drag formulas next to the dynamic array (could use "=IF(C2=C1,B1+1,1)" and copy paste). 

 

I thought that Sequence would likely solve this with some creativity, but was unsuccessful in triggering it to reset at zero when the Customer-Item field changed. After looking over forums for a while, I stumbled upon this post about circular references in dynamic arrays, where Patrick2788 recommended the Scan function. With a very slight modification to his answer, as well as the addition of a dynamic helper column, the solution was identified. Patrick, you're a life-saver.

 

 

The helper, which is the Dynamic Offset Comparison formula, is 

=IF((OFFSET(C2:C26,-1,0,COUNTA(C2:C26),1)=C2:C26),1,0)

 

The Dynamic Counter formula, which is really the heart of the problem, is

=SCAN(1,H2#,LAMBDA(v,a,a+v*a))+1

 

The Dynamic Rank is simply concatenating those two formula, and then there's a check to show it working.

 

Would be curious to hear if this is helpful, and alternatives to solving this. I've only been using dynamic ranges for a few months, and am still very green with LAMBDA, but I'm excited to learn more ways to use it.

 

excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Oct 04, 2024

    mswartzentruber 

    =DROP(

    REDUCE(0,SEQUENCE(ROWS(C2:C26)),

    LAMBDA(u,v,

    VSTACK(u,COUNTIF(TAKE(C2:C26,v),INDEX(C2:C26,v))

    ))),1)

     

    LAMBDA and REDUCE should do what you are looking for if i correctly understand the question.

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Oct 04, 2024

    mswartzentruber 

    Two ways to do it depending on your setup.

     

    with a table:

    =LET(k, COUNTIFS($B$2:B2, B2, $C$2:C2, C2), B2 & "-" & C2 & "-" & k)

    with dynamic array:

    =LET(
    i, SEQUENCE(ROWS(customer)),
    counter, LAMBDA(i, each_customer, each_item,
        LET(
            k, COUNTIFS(TAKE(customer, i), each_customer, TAKE(item, i), each_item),
            each_customer & "-" & each_item & "-" & k
        )
    ),
    MAP(i, customer, item, counter)
)

     

    The second option is more exciting but the first option may be more sensible if the customer data is being used as data entry primarily.

  • djclements's avatar
    djclements
    Bronze Contributor
    Oct 06, 2024

    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...

Resources