Ranking by How many times an item has been mentioned

[VALVe101]

Hello All,

I have a Delima here where I need to give a rank for each time a duplicate text has been mentioned, something like a top-down scan.


in the picture attached, what I'm trying to do is not count how many times the name John has been mentioned rather than actually scanning from the top to bottom and count them each time it sees the name john, it adds an accumulation.

is there a formula that does that?

 

[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’)

How about

=countifs(A$2:A2,A2)

 

[VALVe101]

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’)

How about

=countifs(A$2:A2,A2)

thanks dude, this helped, I counted from the row of the item upwards, and it provided a ranking.

 

[alansidman]

An alternative is power query

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

Book8
	A	B	C	D
1	Name		Name	Count.Index
2	John		John	1
3	John		John	2
4	Matt		John	3
5	James		Matt	1
6	John		James	1
Sheet1

 

[Fluff]

thanks dude, this helped

Glad to help & thanks for the feedback.

 

[VALVe101]

a good one also, as the formula is done on more than 20K rows and it is slowing the sheet down.

An alternative is power query

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

Book8
	A	B	C	D
1	Name		Name	Count.Index
2	John		John	1
3	John		John	2
4	Matt		John	3
5	James		Matt	1
6	John		James	1
Sheet1

 

[Fluff]

Maybe you missed this part of original post

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’)

If you are happy with a separate section showing the counts, that can be done very simply with a formula, depending of your version of Excel.

 

[VALVe101]

Maybe you missed this part of original post

If you are happy with a separate section showing the counts, that can be done very simply with a formula, depending of your version of Excel.

done, updated

 

[Fluff]

Thanks for that.
With 2016 & over 20k rows, you are probably better of with the PQ solution, or a pivot table.