Find the Ranking of Companies

[Please_H]

Dear All,

I am in need of Finding the Ranking of Top 40 Companies among a list of Companies...

I shall explain the example below,

	Month	Year	Company Name	Revenue		2020	Revenue	Rank
	Jan-20	2020	A	50.00		A	90.00			D	370.00
	Jan-20	2020	B	100.00		B	210.00	3		C	365.00
	Jan-20	2020	C	75.00		C	365.00	2		B	210.00
	Jan-20	2020	D	25.00		D	370.00	1
	Jan-20	2020	E	15.00		E	155.00
	Jan-20	2020	A	10.00
	Jan-20	2020	B	90.00		2021		Rank
	Feb-20	2020	C	110.00		A	290.00			B	1,310.00
	Feb-20	2020	D	125.00		B	1,310.00	1		D	470.00
	Feb-20	2020	E	25.00		C	465.00	3		C	465.00
	Feb-20	2020	A	15.00		D	470.00	2
	Feb-20	2020	B	10.00		E	255.00
	Feb-20	2020	C	90.00
	Feb-20	2020	D	110.00
	Feb-20	2020	E	25.00
	Feb-20	2020	A	15.00
	Aug-20	2020	B	10.00
	Aug-20	2020	C	90.00
	Aug-20	2020	D	110.00
	Aug-20	2020	E	90.00
	Jan-21	2021	A	150.00
	Jan-21	2021	B	1,100.00
	Jan-21	2021	C	175.00
	Jan-21	2021	D	125.00
	Jan-21	2021	E	115.00
	Jan-21	2021	A	110.00
	Jan-21	2021	B	190.00
	Feb-21	2021	C	110.00
	Feb-21	2021	D	125.00
	Feb-21	2021	E	25.00
	Feb-21	2021	A	15.00
	Feb-21	2021	B	10.00
	Feb-21	2021	C	90.00
	Feb-21	2021	D	110.00
	Feb-21	2021	E	25.00
	Feb-21	2021	A	15.00
	Aug-21	2021	B	10.00
	Aug-21	2021	C	90.00
	Aug-21	2021	D	110.00
	Aug-21	2021	E	90.00

The Data set is almost identical to the above and its a table.

What I need is a Formula to work with, determine the Rank within an year as well as identifying Quarter-wise ( meaning Jan-Mar as Q1, Apr-Jun as Q2 ) like-wise.

Any help would be highly appreciated.

Thank you.

 

[lrobbo314]

Here is a way with Power Query.

Book1
	A	B	C	D
1	Year	Company Name	Total	Rank
2	2020	D	370	1
3	2020	C	365	2
4	2020	B	210	3
5	2021	B	1310	1
6	2021	D	470	2
7	2021	C	465	3
Sheet5

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Types = Table.TransformColumnTypes(Source,{{"Month", type date}}),
    Group = Table.Group(Types, {"Year", "Company Name"}, {{"Total", each List.Sum([Revenue]), type number}}),
    SubGroup = Table.Group(Group, {"Year"}, {{"Count", each _, type table}}),
    SR = Table.TransformColumns(SubGroup, {{"Count", each Table.AddIndexColumn(Table.Sort(_,{"Total",Order.Descending}),"Rank",1)}}),
    Expand = Table.ExpandTableColumn(SR, "Count", {"Company Name", "Total", "Rank"}, {"Company Name", "Total", "Rank"}),
    Top3 = Table.SelectRows(Expand, each [Rank] <= 3)
in
    Top3

 

[Julien888]

Hi, otherwise with Excel formulas, you can write in cell I3: =SUMIFS(F:F,E:E,H3,D:D,H$2) (for the revenue)

and in cell J3: =RANK(I3,I$3:I$7) (for the ranking)

For quarterwise, I guess the easiest would be to do the same but instead of writing 2020, 2021... write something like 2020Q1, 2020Q2...

 

[Please_H]

Here is a way with Power Query.

Book1
	A	B	C	D
1	Year	Company Name	Total	Rank
2	2020	D	370	1
3	2020	C	365	2
4	2020	B	210	3
5	2021	B	1310	1
6	2021	D	470	2
7	2021	C	465	3
Sheet5

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Types = Table.TransformColumnTypes(Source,{{"Month", type date}}),
    Group = Table.Group(Types, {"Year", "Company Name"}, {{"Total", each List.Sum([Revenue]), type number}}),
    SubGroup = Table.Group(Group, {"Year"}, {{"Count", each _, type table}}),
    SR = Table.TransformColumns(SubGroup, {{"Count", each Table.AddIndexColumn(Table.Sort(_,{"Total",Order.Descending}),"Rank",1)}}),
    Expand = Table.ExpandTableColumn(SR, "Count", {"Company Name", "Total", "Rank"}, {"Company Name", "Total", "Rank"}),
    Top3 = Table.SelectRows(Expand, each [Rank] <= 3)
in
    Top3

Thank you very much for your reply,
Unfortunately the answer is stacked and that wouldn't work with the setup I have...

So does the Macro of yours rearrange the Data or does it recreate the info to a different sheet?

 

[Please_H]

Hi, otherwise with Excel formulas, you can write in cell I3: =SUMIFS(F:F,E:E,H3,D:D,H$2) (for the revenue)

and in cell J3: =RANK(I3,I$3:I$7) (for the ranking)

For quarterwise, I guess the easiest would be to do the same but instead of writing 2020, 2021... write something like 2020Q1, 2020Q2...

Thank you very much for the reply bro...
But I would really love to have the Rankings to be not only shown but also reflected. ( Meaning - sorted )

 

[Please_H]

Hi, otherwise with Excel formulas, you can write in cell I3: =SUMIFS(F:F,E:E,H3,D:D,H$2) (for the revenue)

and in cell J3: =RANK(I3,I$3:I$7) (for the ranking)

For quarterwise, I guess the easiest would be to do the same but instead of writing 2020, 2021... write something like 2020Q1, 2020Q2...

The Output should be similar to this and Dynamic which means as I update info to the Table, it needs to continue sorting the Rank...
Like below,

D	370.00
C	365.00
B	210.00
B	1,310.00
D	470.00
C	465.00

 

[Please_H]

Probably I didn't explain the issue well enough - I am sorry for that...

So let me explain what I am working with,

What I have and what needs to be done,

1. Years running from 1998 to date
2. Over 100+ Companies working with us as Business Agents
3. Every year we rank them and quarterly send a report to them where they stand among Rankings to encourage them to Hit within the first 40 Spots to get higher commissions ( So the report has 6 Manual Tables Done. One gives the Highest 40 by Date, 4 by Quarters, and 1 by the year end )
4. So it needs to be Dynamic as I keep entering info to the Table

What I do,

1. I run a pivot ( selecting the for the Year and sort them by revenue as of a specific Date, by the end of a Quarter or by the end of an year and send them the Report.

 

[Julien888]

Hi, I guess my solution is dynamic, however indeed I forgot to sort the rank instead of just showing it, for this the easiest would be if you could reorder your columns to put the rank to the left, then the year with A, B, C... below and then the revenue to the right, then to sort them you just need a VLOOKUP where you look for value 1, 2 and 3, so row 1 will indeed find company D with revenue 370, row 2 will find company C and so on.