Dynamic Category Sequencing Formula -- 2
Budget: ₹100 – ₹400 INR
I have a table in Excel where Column A holds the category for each record and another column records the date. I need a single, reusable formula that writes an ID in the exact format “[sequence]/[total in category]”.
Key behaviours I’m after
• The number to reset to 1 whenever the category in Column A changes and to keep counting until that category ends.
• The total in the denominator to reflect the full count of that category, even after I sort the sheet or insert new rows.
• Sorting will usually be by Date, so the sequence must remain stable against that order.
Practical constraints
– Roughly 6–10 categories at any time, but the formula must adapt automatically when we add or delete categories or individual rows.
– I’d prefer a pure-formula approach (I expect this will involve a mix of COUNTIFS, SEQUENCE, UNIQUE or LET), but I’m willing to accept a lightweight VBA helper if that is the only way to guarantee stability.
– Compatibility has to be solid on Microsoft 365 desktop.
Deliverable
Provide the final formula, a brief explanation of how it works, and a quick test sheet or clear instructions so I can drop it straight into my file and see the numbering appear as “[sequence]/[total]” beside every row.
Key behaviours I’m after
• The number to reset to 1 whenever the category in Column A changes and to keep counting until that category ends.
• The total in the denominator to reflect the full count of that category, even after I sort the sheet or insert new rows.
• Sorting will usually be by Date, so the sequence must remain stable against that order.
Practical constraints
– Roughly 6–10 categories at any time, but the formula must adapt automatically when we add or delete categories or individual rows.
– I’d prefer a pure-formula approach (I expect this will involve a mix of COUNTIFS, SEQUENCE, UNIQUE or LET), but I’m willing to accept a lightweight VBA helper if that is the only way to guarantee stability.
– Compatibility has to be solid on Microsoft 365 desktop.
Deliverable
Provide the final formula, a brief explanation of how it works, and a quick test sheet or clear instructions so I can drop it straight into my file and see the numbering appear as “[sequence]/[total]” beside every row.