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.

​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.   Read More

​