Home > Enterprise >  Sequential number based on repeating value in another column - Google Sheets
Sequential number based on repeating value in another column - Google Sheets

Time:03-28

this one seems super simple but I'm having a tough time figuring it out, any help would be greatly appreciated.

I have repeating data in Column A, in Column B I need sequential numbering unless the previous row has a repeat value, in which case it would repeat that number in the sequence. Example below.

Is this possible in a single cell array formula?

Column A    Column B
7648490     1 
7634199     2
7631608     3
7620465     4
7620465     4
7616976     5
7601241     6
7601241     6
7601241     6
7601241     6
7599651     7
7597439     8
7597376     9
7596068     10
7596068     10
7596068     10
7596068     10
7596068     10
7596067     10

CodePudding user response:

Delete everything from Col B (including the header) and place the following formula in B1:

=ArrayFormula({"Header";IF(A2:A="",,VLOOKUP(A2:A,{UNIQUE(FILTER(A2:A,A2:A<>"")),SEQUENCE(COUNTA(UNIQUE(FILTER(A2:A,A2:A<>""))))},2,FALSE))})

This will create header text (which you can change as you like within the formula itself) and will produce the result for each row.

The virtual array formed between the curly brackets { } creates a pairing of each UNIQUE value from Col A with an incremental SEQUENCE that starts at 1. Then VLOOKUP just finds each actual value from Col A within the virtual array and returns the SEQUENCE number.

  • Related