0

I've been retooling some older spreadsheet tools for filtering and formatting dynamic data outputs using Excel's newer Dynamic Array Formulas functionality. This has been helping remove some of the need for pre-allocating cells and lower amounts of helper columns (which has allowed for reduced file sizes and snappier performance).

One function type I am struggling to replace is pulling out dynamic, running duplicate counts.

For instance, say I have a column B of 20 names that can vary in length from a handful to say 200 names. There is also related data in columns C, D, etc that similarly varies in size. For use of filtering the Data in the later columns, we currently use a helper column in A consisting of the running count of the duplicates in A with a formula using semi-anchored ranges(ie. Beginning the range with an anchored cell that expands as the formula is copied down the helper column akin to the solution here with CountIf() and a semi-anchored range). The drawback here vs the new dynamic array formulas is that the helper column needs to be pre-allocated for the data.

enter image description here

Despite attempts with Index(), Aggregate(), Filter(), and a few more involved notations like Sumproduct(--(...)), the most straightforward method I can find to make helper column A seems to be by creating the running count via semi-anchored ranges, which unfortunately does not seem to translate well to the new dynamic array Formulas.

Has anyone had any luck adapting the use of semi-anchored ranges and formulas for use in dynamic array formulas?

Improve this question
0

2 Answers 2

Reset to default
2

To use the dynamic array formula we need to use OFFSET which is volatile.

=COUNTIFS(OFFSET(A1,0,0,SEQUENCE(COUNTA(A1#))),A1#)

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

Interesting, so in order to use this, the data in column A will need to already be a Spill result from the formula in A1, correct? Is there a way to refer to the data of column A without already coming from a Spill result? Worst case, I could imagine adding a helper column B and setting B1 to: =A1:INDEX(A:A,COUNTA(A:A)) and then making C1 =COUNTIFS(OFFSET(B1,0,0,SEQUENCE(COUNTA(B1#))),B1#), or by combining the two in one long formula... Just curious if there was another way. In the meantime, I think this is the most straightforward answer!
No you could do: =COUNTIFS(OFFSET(A1,0,0,SEQUENCE(COUNTA(A1:A7))),A1:A7)
or dynamic: =COUNTIFS(OFFSET(A1,0,0,SEQUENCE(COUNTA(A:A))),A1:INDEX(A:A,COUNTA(A:A)))
So as some followup, I attempted to convert the use of offset to be non-volatile with some interesting results. (1) First, if I remove only the use of Sequence() from the above height parameter (ie leave just Count(A1#), the counting behavior breaks as shown here (2) If I change to just referring to A1# without offset altogether (ie =COUNTIFS(A1#,A1#), it returns the same result. (3) This stays consistent with the range reference tweaks in the prior comments.
Looking at the use of offset and sequence, the array in CountIfs() becomes an array of #VALUE! errors that count out correctly when using the original array as the criteria. Is there a way to replicate this effect using index maybe?
|
1

Appreciate this is an old post, but for future reference (I personally at least couldn't find an answer elsewhere), the below seems to work as a non-volatile formula alternative.

=LET(InputArray,A1#,
RowCount,ROWS(InputArray),
Temp,1*(InputArray=TRANSPOSE(InputArray)),
MMULT(TRANSPOSE(IF(SEQUENCE(RowCount,1)>SEQUENCE(1,RowCount),0,Temp)),SEQUENCE(RowCount,1,1,0)))
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.