Sorting Number from 1 Continuously and Differently on Each String

Question

I have a sort of data that can be seen here:

My data is sorted like that in column A from A to Z.

Is there any way to make a sorting number from 1 to any number and reset it to 1 again when column A changes value, just like in column B?

I have tried using MMULT but is there any other way? Since MMULT requires a ton of time to calculate.

ArrayFormula is preferred since it is very convenient to be able to auto-update the return when the value is updated.

score 2 · Answer 1 · answered Dec 27 '22 at 16:34

2

try:

=INDEX(IFERROR(1/(1/COUNTIFS(A1:A, A1:A, ROW(A1:A), "<="&ROW(A1:A)))))

answered Dec 27 '22 at 16:34

player0

score 1 · Accepted Answer · answered Dec 28 '22 at 09:22

This is faster than countifs if you have large amounts of data - plz see this question

=lambda(FILTER,scan(,sequence(rows(filter)),
lambda(a,c,if(c=1,1,if(index(filter,c)=index(filter,c-1),a+1,1)))))
(filter(A:A,A:A<>""))

My answer shows a solution for pre-sorted data: the reference above works for unsorted data.

2 Answers2