Cumulative count

[pepe74287]

Hello,

I'm looking for a solution how to add cumulative count column. So for instance 1929 was won by Barcelona (first title). 1929-30 by Bilbao (first title), 1930-31 again by Bilbao (thus second title), 1931-32 by Real, thus first title and so on.

Dataset:
1929 Barcelona
1929-30 Athletic Bilbao
1930-31 Athletic Bilbao
1931-32 Real Madrid
1932-33 Real Madrid
1933-34 Athletic Bilbao
1934-35 Betis
1935-36 Athletic Bilbao

Desired output:
1929 Barcelona 1
1929-30 Athletic Bilbao 1
1930-31 Athletic Bilbao 2
1931-32 Real Madrid 1
1932-33 Real Madrid 2
1933-34 Athletic Bilbao 3
1934-35 Betis 1
1935-36 Athletic Bilbao 4


Thanks
Pepe

 

[123rickfear]

assuming all your data is in the format as above.

in column B put the formula =RIGHT(A1, LEN(A1)-8), and drag down. (this won't work for your first entry, so you may need to type over).

This should then give you just the team name.

In column C, if you put the formula =COUNTIF($B$2:B2,B2), and drag down.

In column D put the formula =A1&" "&C1.

This should give you your desired output.

Hope this helps.

Rick

 

[pepe74287]

assuming all your data is in the format as above.

in column B put the formula =RIGHT(A1, LEN(A1)-8), and drag down. (this won't work for your first entry, so you may need to type over).

This should then give you just the team name.

In column C, if you put the formula =COUNTIF($B$2:B2,B2), and drag down.

In column D put the formula =A1&" "&C1.

This should give you your desired output.

Hope this helps.

Rick

Hi Rick,

Thanks, I forgot to mention I'm looking for Power Query solution (apologise for that).

 

[ImkeF]

You can reuse your newly acquired grouping-technique here
Just instead of nesting a skip-operation, you nest in an Index at group-level, starting at 1:

= Table.Group(#"Added Index", {"ColumnWithTeamName"}, {{"Count", each Table.AddIndexColumn(_, "CumCount", 1, 1), type table}})

Provided that your data comes in sorted like in your example, that's all: You index will count correctly.

But in order to be on the safe side, you can sort the table before.
Also if you want to maintain the order of your data as it came in, you need to add an index with this method that you're going to sort after the expansion.

LinkToFile

 

[pepe74287]

Wow, that was easy! Again

I was looking for solution to this problem on many sites, but all solutions were too complicated and I was almost sure there must be much easier way how to accomplish what I want. And here you go

Many thanks

Pepe