generate sequence like values from a column that generate the same sequence-value for the same column-value

Question

I know, the title is an absolute desaster, but I dont know how to describe what I want with just a few words :(

Assume that I have the following table:

column a	column b
87	1
11	2
87	1
10	3
3831	4
6732541	5
7719	6
87	1
11	2

The first column is the data where I want to improve readability. To be more precise, readability in case of duplicate values but distinguish between different values. The numbers there can be quite large (eight digits) but its not a sequence, so there can (and will) be gaps as the example shows. I am looking for a way to transform the values from column a to a sequence starting with 1 where the same value of column a gets the same value in column b. Sounds strange, but I dont know how to explain it better.

Example:

• The value 87 (1st, 3rd and 8th row) in column a gets value 1 in column b
• The value 11 (2nd and 9th row) gets value 2 in column b
• All other values in column a are unique and therefore their values in column b are unique too. In the best case, the unqiue values in column a wouldnt even get a value in column b at all.

Preferably without scripting because there are multiple users on the sheet and most of them have no coding/scripting experince.

Answer 1

Use countif() to identify rows where the value in column A is unique, if() to show a blank on those rows, and vlookup() to show an ordinal on the remaining rows:

=arrayformula( 
  if( 
    countif(A2:A, A2:A) <= 1, 
    iferror(1/0), 
    vlookup( 
      A2:A, 
      { 
        unique(A2:A), 
        sequence(rows(unique(A2:A))) 
      }, 
      2, false 
    )
  ) 
)

Answer 2

If you've got 'column A' in A1 with the list of values under it, place the following in B2:

=arrayformula(vlookup(A2:A10,{unique(A2:A10),sequence(countunique(A2:A10))},2,FALSE))

We are creating a virtual array containing the list of unique values in column A, adjacent to which is a number corresponding to the order they are found in column A; we then do a vlookup of column A against this array to get the relevant number for each entry.