title: Excel Dynamic Array Running Count of Duplicates
body: <p>I've been retooling some older spreadsheet tools for filtering and formatting dynamic data outputs using Excel's newer <a href="https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531" rel="nofollow noreferrer">Dynamic Array Formulas</a> 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).</p>
  <p>One function type I am struggling to replace is pulling out dynamic, running duplicate counts.</p>
  <p>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 <a href="https://stackoverflow.com/questions/13698632/how-do-i-enumerate-duplicate-rows-in-excel"><code>CountIf()</code> and a semi-anchored range</a>). The drawback here vs the new dynamic array formulas is that the helper column needs to be pre-allocated for the data.</p>
  <p><a href="https://i.stack.imgur.com/nav3T.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/nav3T.png" alt="enter image description here" /></a></p>
  <p>Despite attempts with <code>Index()</code>, <code>Aggregate()</code>, <code>Filter()</code>, and a few more involved notations like <code>Sumproduct(--(...))</code>, 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.</p>
  <p>Has anyone had any luck adapting the use of semi-anchored ranges and formulas for use in dynamic array formulas?</p>