find and list top 10 items in a table showing their row and column labels?

[merlin777]

I have a table which summarises the number of sales of tshirts of each colour combination (its actually much bigger than this)

I'd like to show a list of the top 10 most popular colour combos so somehow i have to find the highest 10 values from these cells and display a list of them, in order, showing both colours and the number of sales they've had. I'm not sure how to go about this. Is there a function for this?


[TABLE="width: 391"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]white[/TD]
[TD]black[/TD]
[TD]red[/TD]
[TD]green[/TD]
[TD]blue[/TD]
[TD]yellow[/TD]
[TD]pink[/TD]
[TD]grey[/TD]
[/TR]
[TR]
[TD]white[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Ice Grey[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Sport Grey[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Vegas Gold[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Natural[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Sand[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Daisy[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Safety Green[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Yellow Haze[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Gold[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Old Gold[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

best sellers:
black on white - 7
blue on gold - 5
black on sand - 3
etc

 

[JoeMo]

I've just noticed something strange is going on.
Its not actually retrning the top number that is being asked for.
When i change the 10 (change to suit) to other numbers this is what i get

ask for top 1 and get a list of 1
2 gives 2
3 = 3
4 = 4
but 5 gives 9
6 = 9
7 = 9
8 = 9
9 = 9
then 10 gives 15
11 = 15

and so on.

?

Need to see the data that's producing those results. Most likely there are more than one of some of the combinations that have the same "score" - i.e. there are ties.

 

[merlin777]

I'm not near my pc but from what I remember that sounds likely.
Is there a way around it? Can we count up to 10 (or whatever) in another way?

 

[merlin777]

It's not actually that serious a problem for me because I'm linking to the top 10 results from a summary page so I won't see the overspill.

 

[JoeMo]

It's not actually that serious a problem for me because I'm linking to the top 10 results from a summary page so I won't see the overspill.

Not sure I understand why you wouldn't want to see all the ties even if that required exceeding 10 items. Isn't it your objective to know all of the items that are selling well?

 

[merlin777]

If I ask for the top 10 and the macro displays 15 items including ties then when I look at the first 10 that will be the top 10 incuding ties in the 10 which is perfect.