Maintain position of value in UNIQUE function

[ncbf87]

Hi guys,

Hope you're well. I'd appreciate some guidance from the pros here.

I've a table of all employee's position title which I need to summarise into a table of unique position titles --> do a count --> and allow manual input in another column what is the required headcount of a position end of year. The challenge comes when a manager may change what a current position title is called, which will then refresh the UNIQUE formula, the sorting of values will differ, and this will mess up the manual input of headcounts

Example (screenshot attached):

Col D (Table 2) = line by line of all available positions in the company

Col F = UNIQUE(Table2[All Positions]))

Col G = COUNTIF(Table2[All Positions],F2#)

Col H = Manual Input

Column H is a data we want to track, maintain, and allow future changes. However, it needs to match the "unique position" line in column F.

If the position title in column D changes (rename, new row), column F will refresh with a change in order of the positions, which will make column H's input redundant.

Is there any alternative to approach this challenge, or a solution to fix the position of the position titles (maybe by having a unique ID for sorting purpose?)

Excel ver: Microsoft 365

 

[jasonb75]

What about Power Query though?

It's not something that I'm familiar enough with to say for certain but I would imagine that it would behave the same as a formula when the query is refreshed.