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.