I'm trying to allocate serial/sequential ID's to the students based on the course they are signed up for, which is specified in another column.

For example, if john is the first one to signup for "English", ID is "E0001", the second person signed up for "English" will be allocated "E0002".

And then, if third person signs up for a different course "Maths", I have to start another new sequence, say "M0001".

I tried several combinations with IF and SEQUENCE, CONCATENATE... but was unable to figure out a proper way of doing this. I would very much appreciate anyone's help.

CodePudding user response：

Try COUNTIFS() applying few tricks.

=Arrayformula(IF(B2:B="",,LEFT(B2:B,1) & "000" & COUNTIFS(B2:B, B2:B, ROW(B2:B), "<="&ROW(B2:B))))