1

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.

1
  • 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

2 Answers 2

2

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

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.

2
  • sounds great. will try tomorrow :)
    – Michael
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.