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

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.

• Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
– Community Bot
Sep 28, 2022 at 21:07

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
)
)
)
``````

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.

• sounds great. will try tomorrow :) Sep 28, 2022 at 20:02
• Nota bene if there are any blank cells within the range, `unique()` will include one of them while `countunique()` will not, causing the { array expression } to error out. Use `rows(unique())` to avoid that. Sep 29, 2022 at 16:05