Conditional Ranking with large data set

[erika791]

Hi Everyone,

I have a large data set that includes multiple columns (Country, Name of Store, year to date sales, growth, etc.) and multiple rows (Australia, China, Hong Kong, etc.). I need to rank each store's sales but only within their country. However, I want the formula to expand all of the countries so I don't have to redo the formula every time a new store is open within a particular country. I've been trying to find a way to automate this because I will have to do this exercise every month.

So far, I've tried SUMPRODUCT per other guidance with the below formula:
=1+SUM(IF($A$6:$A$1800=A6,IF((G6+ROW(G6)*0.00001)>($G$6:$G$1800+ROW($G$6:$G$1800)*0.00001),1,0),0))

However, it still gives me rankings for all countries. For example, if I am trying to rank the stores in China, it will still pop-up ranks for the stores in Australia. Ideally, I can use a formula that will just give me blanks when the rank formula is on the country that I am currently not ranking.

Is there a "rankif" type formular that I could use for this?

[ChemistB]

How about using a pviot Table? I used "Affiliate" as "Report Filter", "Retailer" as a row label and FY16 YTD actual in SUM Values twice (once as SUM, once as RANK Large to small). See attachment

[erika791]

Hi - yes my original intent was to use a pivot table but there are about 10-20 new lines of data each month (as we open new stores) so the pivot table would have to be re-done every month, right? not ideal.

[ChemistB]

We are just using Column G to rank? Try this formula

=IF(A2="Australia", SUMPRODUCT(--($A$2:$A$138= "Australia"), --(G2<$G$2:$G$138)),"")

If you set up your original data as a TABLE (Insert Table), then the data will automatically update. You would just need to "Refresh" the pivot table.

[erika791]

Hi - thanks! Unforunately the formula didn't work. I just kept getting an error message.

However, did not know about being able to set up my data as a table. So, anytime I import new data, as long as I'm pasting as values into the table, I won't have to recreate the pivot table, correct? That seems like the easiest solution!

[ChemistB]

That is correct. As long as you right click on the pivot table and hit refresh, it should encompass all data.

Here's the spreadsheet with my formula solution.

[erika791]

ah that works! Don't know why it wasn't working originally.

Thanks!

[erika791]

Actually - one last question. the ranking is coming in with duplicates (e.g., of two stores had 100K in sales, they're both coming in at rank 9). Is there a way to incorporate a tie brake into the formula?

[ChemistB]

Sure, what do you want the tie break to be?

[ChemistB]

PS. There's a way to do almost anything. Often it's just not worth it.

[erika791]

haha. just tiebrake to the next number. no decimals. Thanks!

[ChemistB]

In L2 copied down,
=IF(A2="Australia", SUMPRODUCT(--($A$2:$A$138= "Australia"), --(G2<$G$2:$G$138))+1+COUNTIF($L$1:$L1,SUMPRODUCT(--($A$2:$A$138= "Australia"), --(G2<$G$2:$G$138))+1),"")

The COUNTIF part I added counts up how many times that rank has appeared before (typically 0) and addis it to the total. So if 11 occurs twice, one is added to the second 11 making it a 12.

[erika791]

Thanks! but this seems to only work once and then it gets stuck on another repeating number again,

[ChemistB]

True, it won't do a triple tie.

[erika791]

is there a way to make it break a triple tie (or multiple ties in general)? tinkering with it now as well

[ChemistB]

I will play around with it.

[ChemistB]

I was overthinking it ( I do that a lot). Try this simplier formula

=IF(A2="Australia", SUMPRODUCT(--($A$2:$A$138= "Australia"), --(G2<$G$2:$G$138))+1+COUNTIF($G$1:$G1,G2),"")