In the Excel table below, there are duplicate values in column A:

The task is to number column A in order with natural numbers while making duplicate values take the same number. Write the corresponding numbers in the next column on the right:

Use SPL XLL to enter the formula below and drag the first cell down all the target cells:

=spl("=?2.conj().id@u().pos(?1)",A1,A$1:A$5)

Or we can use a dynamic array formula to get the results at a time:

=spl("=(y=(x=?1.conj()).id@u()),x.(y.pos(~))",A1:A5)

In the above formula, conj()function concatenates values in the specified range of cells into a one-dimensional arrary; id() function get the unique members and works with @u option to not to re-sort the array; pos() function get the position of each member.

Source:https://stackoverflow.com/questions/78400711/excel-transforming-automatically-a-cell-based-on-the-input-in-another-one