Excel Dropdown list generates unique ID number for each value

Question

I have an Excel spreadsheet with two columns. Column A is filled with the same DropDown list that contains "Archive, Directory, Cloud".

Column B must be filled with ID depending on the value of Column A.

The IDs are different from each value of the list. All the values start from 1 and goes on for each time I select that value from the list.

Column A      Column B

Directory        1
Archive          1
Cloud            1
Archive          2
Archive          3
Directory        2

Just like in the example, once I select from the list (in column A), column B fills automatically with the next number.

Note that I need this to work on all the spreadsheet tables. If in a table I use the same dropbox, the number next to it has to be the next and not again 1.

Answer 1

You can use Countif for your solution (if I have understtod the problem correctly):

for example for cell B2: COUNTIF($A$1:A2,A2) and then dragdown to all the cells in your column B and this will give the required result.