Ranking a large a mount of data - want dupes included

[Laurence83]

Hi,

I've got a spreadsheet which has over 9,000 hotels on it & i use Sumifs and Countifs to work count-up the number of bookings and bed nights at each property. I've got to circulate this report and as you can imagine, looking through 9,000 properties isn't very interesting! So what i do is narrow it down to the top 50 most improved and the bottom 25 worst performers (compared to their performance over the same period last year.

I thought i'd cracked it by using this formula to find the largest number =LARGE('Property Stats'!Q$4:Q$9631,A138) & then the following formula in array {=MAX(IF('Property Stats'!Q$4:Q$9631<F138,'Property Stats'!Q$4:Q$9631))} to get the next 49. I'd then use this formula to get the name of the hotel =INDEX('Property Stats'!B$4:B$9631,MATCH($F138,'Property Stats'!$Q$4:$Q$9631,0),0). And then vlookups off the hotel name to get any other data i wanted.

But..... I've just found out (after thinking this worked fine last quarter) that the array formula is missing out some properties, because if for example the 10th ranked property has a value of 100 and the 11th ranked property also has a value of 100 it'll only pull through the 10th ranked property and then move on to the next unique value.

Has anyone come across a similar problem or has any suggestions?

If any of this ins't clear please let me know (this is my first time posting here - but I've used this forum a lot to help me out)

Thanks

Laurence

 

[anglais428]

Without knowing the layout of your dataset, you could have a column with the formula =RANK.EQ(cell,data_range)
If you work out the growth and then rank the growth of each individual hotel against the rest then any equal numbers (e.g. 5%) would be ranked equally.
Then just sort by the rank and you'll have your top 50.

 

[Marcelo Branco]

Welcome to Mr Excel

See if this example helps
Data in columns A:B
Objective --> list the Top5 Scores and Hotels


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Hotel​

[/TD]
[TD]

Score​

[/TD]
[TD][/TD]
[TD]

5th Largest​

[/TD]
[TD]

Count​

[/TD]
[TD]

Top Scores​

[/TD]
[TD]

Hotel List​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Hotel1​

[/TD]
[TD]

20​

[/TD]
[TD][/TD]
[TD]

20​

[/TD]
[TD]

6​

[/TD]
[TD]

30​

[/TD]
[TD]

Hotel3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Hotel2​

[/TD]
[TD]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

26​

[/TD]
[TD]

Hotel10​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Hotel3​

[/TD]
[TD]

30​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

22​

[/TD]
[TD]

Hotel4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Hotel4​

[/TD]
[TD]

22​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

22​

[/TD]
[TD]

Hotel8​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Hotel5​

[/TD]
[TD]

16​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

20​

[/TD]
[TD]

Hotel1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Hotel6​

[/TD]
[TD]

19​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

20​

[/TD]
[TD]

Hotel7​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

Hotel7​

[/TD]
[TD]

20​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

Hotel8​

[/TD]
[TD]

22​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

Hotel9​

[/TD]
[TD]

18​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

Hotel10​

[/TD]
[TD]

26​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in D2
=LARGE(B2:B11,5)

Formula in E2
=COUNTIF(B:B,">="&D2)
as you can see there are duplicates scores and so more than 5 hotels with scores>= 5th largest

Formula in F2 copied down
=IF(ROWS(F$2:F2)>$E$2,"",LARGE($B$2:$B$11,ROWS(F$2:F2)))
observe the duplicates scores

Array formula in G2 copied down
=IF(F2="","",INDEX($A:$A,SMALL(IF($B$2:$B$11=F2,ROW($A$2:$A$11)),COUNTIF(F$2:F2,F2))))
confirmed with Ctrl+Shift+Enter

Hope this helps

M.

 

[Laurence83]

thanks for the replies guys, I've been diverted away so not had a chances to fully test, but the rankings method by Marcelo works a treat, i'm just struggling with the Array formula to match up the hotel names - i'm sure it's something I've done, so will revisit it once time permits.

 

[Marcelo Branco]

Array formulas must be confirmed with Ctrl+Shift+Enter simultaneously.
If you are not familiar with array formulas take a look at
Array Formulas

M.

 

[Laurence83]

thanks Marcelo, yes i have used array before (i was using one with this data), but i imagine it's just a column out or something simple - once i can get back to the sheet i'll revisit it and contact if i'm still having issues, thanks again.

 

[Laurence83]

Hi,

I've been able to go back to this task this morning & i'm having an issue with the indexing, for some reason, that i can't fathom the indexing isn't working https://drive.google.com/file/d/0B-CeH_iteI5gdjBPeEtsSFBqYTQ/view?usp=sharing (this is a copy saved with data removed to protect annonymity). It's column b that i'm having a problem with, the formula, as you'll see is just taking the hotels in the order they are sorted, rather than referencing the variance (column I in property stats and column face on the face), (you'll see if you sort the data differently in the product stats, the face data changes).

Any assistance that can be provided will be greatly received, if any more info is needed please let me know.

Thanks

 

[Marcelo Branco]

You are using a wrong formula - observe carefully the array formula in G2, post #3

Try
=IF(F4="","",INDEX('Property Stats'!$B:$B,SMALL(IF('Property Stats'!$I$4:$I$9631=F4,ROW('Property Stats'!$B$4:$B$9631)),COUNTIF(F$4:F4,F4))))

Ctrl+Shift+Enter

M

 

[Laurence83]

that's great, thanks very much Marcelo

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.