CODEWITHSUNDEEP
google-sheetsenumerateranking-functions
Igor Bobritskiy
Igor Bobritskiy

Reputation: 3

How to enumerate categories with formula in Google Sheets

I'm struggling to get solution for my simple task in Google Sheets: I want to use formula to enumerate categorical data from 1 to number of entries in category, then starting from 1 for next category etc. Data is already sorted

I've tried SCAN function like =SCAN(0; A:A; LAMBDA(counter; current; IF(INDEX(A:A; ROW()-1)=current); counter+1;1))) but problem is counter doesnt restart from 1 from the next category.

result should be like this:

A 1 A 2 A 3 B 1 B 2 C 1 C 2 C 3

Upvotes: 0

Views: 58

Answers (2)

z..
z..

Reputation: 13056

You can try:

=MAP(A1:A,LAMBDA(_,IF(_="",,COUNTIF(A1:_,_))))

Without LAMBDA:

=ARRAYFORMULA(LET(a,A:A,r,ROW(a),IF(a="",,COUNTIFS(a,a,r,"<="&r))))

Upvotes: 0

Kreeszh
Kreeszh

Reputation: 809

Here's something you can try, considering the data is already sorted.

=let(
  z,tocol(,1),
  categories,unique(tocol(A:A,1)),
    breakout,map(categories,lambda(k,countif(A:A,k))),
      reduce(z,breakout,lambda(a,c,vstack(a,sequence(c,1,1,1)))))

Upvotes: 0

Related Questions