Ranking Query

[CarlCarlos]

Dear all,

I have a query with ranking. I have a list (a simplified one is below) that covers 340 different store numbers and is over 10,000 lines of data.

Each store could potentially have many different entries.

Store Sales Rank
1 10 1
5 15 1
1 2 2
3 9 2
5 -5 2
3 10 1

What i need is a rank formula that ranks each store independently but in the same column as exampled above, Can any one please help?

 

[Comfy]

Rubbish.

 

[Comfy]

Alittle more googling and I stumbled upon:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Store</td><td style=";">Sales</td><td style=";">Rank</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">10</td><td style="text-align: right;color: #333333;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;color: #333333;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">3</td><td style="text-align: right;;">9</td><td style="text-align: right;color: #333333;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">10</td><td style="text-align: right;color: #333333;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">5</td><td style="text-align: right;;">15</td><td style="text-align: right;color: #333333;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">5</td><td style="text-align: right;;">-5</td><td style="text-align: right;color: #333333;;">2</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$2:$A$7=A2</font>),--(<font color="Red">B2 < $B$2:$B$7</font>)</font>)+1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$2:$A$7=A3</font>),--(<font color="Red">B3 < $B$2:$B$7</font>)</font>)+1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C4</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$2:$A$7=A4</font>),--(<font color="Red">B4 < $B$2:$B$7</font>)</font>)+1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C5</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$2:$A$7=A5</font>),--(<font color="Red">B5 < $B$2:$B$7</font>)</font>)+1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C6</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$2:$A$7=A6</font>),--(<font color="Red">B6 < $B$2:$B$7</font>)</font>)+1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C7</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$2:$A$7=A7</font>),--(<font color="Red">B7 < $B$2:$B$7</font>)</font>)+1</td></tr></tbody></table></td></tr></table><br />

 

[CarlCarlos]

Thank you for this. One issue is that if the sales value is the same for several entries of the same store then the same rank is returned. is there a way to have it so that the rank can only be 1,2,3,4,5 etc not 1,1,1,1,4,5 etc?

 

[Comfy]

Are you saying there are duplicates in your information?

Or do you also have another column i.e. Dates?

 

[CarlCarlos]

There are duplicates of the data

i.e.

Store Sales
1 10
1 10
1 10
1 10
1 10

The data could very well be like above, but in the interest of using the rank to index from 1,2,3,4,5 would be the required rank

 

[Comfy]

There are duplicates of the data

i.e.

Store Sales
1 10
1 10
1 10
1 10
1 10

The data could very well be like above, but in the interest of using the rank to index from 1,2,3,4,5 would be the required rank

And what would dictate which of those results would be 1 or 2 or 3?

 

[CarlCarlos]

Just the order in which the come in the list will be more than fine

 

[Comfy]

=IF(COUNTIFS($A$2:$A$11,A2,$B$2:$B$11,B2)>1,SUM(($A$2:$A2=A2)*($B$2:$B2=B2)),SUMPRODUCT(--($A$2:$A$7=A2),--(B2 < $B$2:$B$7))+1)

Entered with Ctrl+Shift+Enter

Asuuming you have xl07 or greater will repost if you do not

 

[Comfy]

IT won't work with

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">4</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">4</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C8</th><td style="text-align:left">{=IF(<font color="Blue">COUNTIFS(<font color="Red">$A$2:$A$11,A8,$B$2:$B$11,B8</font>)>1,SUM(<font color="Red">(<font color="Green">$A$2:$A8=A8</font>)*(<font color="Green">$B$2:$B8=B8</font>)</font>),SUMPRODUCT(<font color="Red">--(<font color="Green">$A$2:$A$11=A8</font>),--(<font color="Green">B8 < $B$2:$B$11</font>)</font>)+1</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C9</th><td style="text-align:left">{=IF(<font color="Blue">COUNTIFS(<font color="Red">$A$2:$A$11,A9,$B$2:$B$11,B9</font>)>1,SUM(<font color="Red">(<font color="Green">$A$2:$A9=A9</font>)*(<font color="Green">$B$2:$B9=B9</font>)</font>),SUMPRODUCT(<font color="Red">--(<font color="Green">$A$2:$A$11=A9</font>),--(<font color="Green">B9 < $B$2:$B$11</font>)</font>)+1</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C10</th><td style="text-align:left">{=IF(<font color="Blue">COUNTIFS(<font color="Red">$A$2:$A$11,A10,$B$2:$B$11,B10</font>)>1,SUM(<font color="Red">(<font color="Green">$A$2:$A10=A10</font>)*(<font color="Green">$B$2:$B10=B10</font>)</font>),SUMPRODUCT(<font color="Red">--(<font color="Green">$A$2:$A$11=A10</font>),--(<font color="Green">B10 < $B$2:$B$11</font>)</font>)+1</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C11</th><td style="text-align:left">{=IF(<font color="Blue">COUNTIFS(<font color="Red">$A$2:$A$11,A11,$B$2:$B$11,B11</font>)>1,SUM(<font color="Red">(<font color="Green">$A$2:$A11=A11</font>)*(<font color="Green">$B$2:$B11=B11</font>)</font>),SUMPRODUCT(<font color="Red">--(<font color="Green">$A$2:$A$11=A11</font>),--(<font color="Green">B11 < $B$2:$B$11</font>)</font>)+1</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

I can't do it I'm afraid, alittle over my head that one.