Rank a List 1-10 only if it it meet a certain criteria

[Kele]

Hi,

I have a list of city that i update weekly to see the revenue that being spent in those city. What i would like is to rank the top 10 city's in my list by week.

I know i could do a pivot table but i would like to not use one as I am on a PC and other users are on a mac and it has problems.

I have a macro that updates weekly but pulls in the current years data, I then have a weekly summary sheet that I would like to pull in only the top 10 city for that week.

I am sure this is just a Rank(IF but I can get it to work

A B C D​

[TABLE="width: 286"]
<tbody>[TR]
[TD="align: center"]city[/TD]
[TD="align: center"]week[/TD]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]Rank[/TD]
[/TR]
[TR]
[TD="align: center"]Aberdeen[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]145[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]Aberdeen[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]24[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]Basildon[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]Basingstoke[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]7[/TD]
[/TR]
[TR]
[TD="align: center"]Bath[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD="align: center"]Bath[/TD]
[TD="align: center"]32[/TD]
[TD="align: center"]89[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]Birmingham[/TD]
[TD="align: center"]32[/TD]
[TD="align: center"]181[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Birmingham[/TD]
[TD="align: center"]32[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]9[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Gary's Student]

If, in a given week, two cities have the same revenue, how do you want them ranked?

 

[Kele]

I am happy for them to have the same rank

 

[Marcol]

Do you need the revenue for each city summed when there is more than one value for a given week no.?

See if this helps for starters, Ranking is by Revenue and in the case of equal revenues, 1/Row Number is used as a tie-breaker.
DragD2:E2 and H2:I2 down as required.

Enter your Week No in E2

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	City	Week No	Revenue	Filter for week >	32		Rank	City	Revenue
2	Aberdeen	30	145				1	Birmingham	181
3	Aberdeen	30	24				2	Bath	89
4	Basildon	30	27				3	Birmingham	5
5	Basingstoke	30	18				4
6	Bath	30	13				5
7	Bath	32	89	89.142857	2		6
8	Birmingham	32	181	181.125000	1		7
9	Birmingham	32	5	5.111111	3		8
10							9
11							10
Sheet1

 

[Marcol]

This will sum multiple entries for any city in any week no.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	City	Week No	Revenue	Sum Duplicates	Filter for week >	32		Rank	City	Revenue
2	Aberdeen	30	145					1	Birmingham	186
3	Aberdeen	30	24	169				2	Bath	89
4	Basildon	30	27	27				3
5	Basingstoke	30	18	18				4
6	Bath	30	13	13				5
7	Bath	32	89	89	89.142857	2		6
8	Birmingham	32	181					7
9	Birmingham	32	5	186	186.111111	1		8
10								9
11								10
Sheet1 (2)

 

[Kele]

thanks for this