Sequential numbering of months without VBA

[Kaarthuul]

I have a spreadsheet I am working on and I have a list of months in one column. I need the next column to be the number of appearance of each month in the list (see attached).

Ideally I would love this to be one formula in B1 that does this dynamically, so that when the list grows/shrinks it adjusts to fit?

Thanks,

Clive

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Kaarthuul]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Done

 

[Fluff]

Thanks for that.
How about

=LET(h,TRIMRANGE(A2:A100),r,ROWS(h),MMULT((SEQUENCE(r)>=SEQUENCE(,r))*(h=TOROW(h)),SEQUENCE(r,,,0)))

 

[Kaarthuul]

Thanks for that.
How about

=LET(h,TRIMRANGE(A2:A100),r,ROWS(h),MMULT((SEQUENCE(r)>=SEQUENCE(,r))*(h=TOROW(h)),SEQUENCE(r,,,0)))

Thanks for that.
How about

=LET(h,TRIMRANGE(A2:A100),r,ROWS(h),MMULT((SEQUENCE(r)>=SEQUENCE(,r))*(h=TOROW(h)),SEQUENCE(r,,,0)))

Perfect, thank you

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Rick Rothstein]

Here is a somewhat shorter formula that will also work...

=SCAN(1,A:.A,LAMBDA(a,x,COUNTIF(A1:x,x)))

If you are not starting at cell A1 (as your picture shows), then try something like this instead...

=SCAN(1,A2:.A999,LAMBDA(a,x,COUNTIF(A2:x,x)))

Note the dot after the first colon in each formula... it is the shortcut notation for TRIMRANGE downward.

 

[Fluff]

Or even shorter

=MAP(A:.A,LAMBDA(x,COUNTIF(A1:x,x)))

 

[alansidman]

and yet one more option with power query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Column1"}, {{"Count", each _, type table [Column1=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Counts",1,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Column1", "Counts"}, {"Column1", "Counts"})
in
    #"Expanded Custom"