Advanced Sorting

[analyst1]

Hi,

Here's a question that I'm stumped on.

I have a list of products in Column A (approximately 50), and a list of Companies (approx 400) in the rest of the colums, with sales volume of each product populating the data.



Company 1 Company 2 Company 3
Product 1 125 250 3
Product 2 250 125 56
Product 3 75 3 450


Each company has a different Top 10 products. Is there a way to sort the top 10 of each company? I'm currently doing it with a pivot table, but because each company is different I have to do it one at a time, and copy it over to another worksheet. I have to do this for 400+ companies - there's got to be a different way!

[Cheeky Charlie]

I think so, but please upload a (trimmed and anonymised) example so I can try out the idea before sending you down a blind alley...

[analyst1]

Attached is an example.
As you can see from the tab "Format When Sorted" I don't need the numbers - just the products sorted by compnay an order.

Seems like there should be an easy way

Mike

[Cheeky Charlie]

Rank the products against company in the raw data, I used:
=rank(B2,B$2:B$41)
in R2, copied down and across to replicate the whole table
Then, on your results page, use the rankings to return the position of the product names:
=INDEX(Raw!$A$2:$A$41,MATCH(COLUMNS($A:A),INDEX(Raw!$R$2:$AG$41,0,ROWS($1:1)),0))
in B3
(a lot more complex-looking than it really is as it also transposes (rows-columns) the results from the original layout)

The only problem is that dupes don't carry through properly...

I'm still thinking on that one - I've had a thought, simple way is:
Raw!R2=RANK(B2,B$2:B$41)+COUNTIF(R$1:R1,RANK(B2,B$2:B$41))

There's probably a more glamorous way, but I'm too tired to think of it

[analyst1]

Thank-you so much for your help so far, I am very grateful.
I've actually changed things around a little bit, and I was able to arrange the list a little different - I've got it ranked properly now. The problem I think I'm having is with the index / match arguments.

So to be clear, now that the sheet "Raw" is ranked; I need to return the column heading corresponding to the top 10 ranked products.

I've physically put in the value in Row2 on "Summary" so you can see what I mean.

I couldn't figure out the match/index stuff so your help is greatly appreciated.

Cheers, Mike

[Cheeky Charlie]

Yeah, the index/match was really complex because I had to transpose the results - should be a lot easier now.

Are the companies (F0A, R0B etc.) always going to match? Can make the formula simpler if so.

Following the previous logic:
Raw DY2
=RANK(B2,$B2:$DW2)+COUNTIF($A2:A2,B2)
Copied across and down to replicate entire table (goes to IT424)
Summary B2
=INDEX(RAW!$B$1:$DW$1,MATCH(COLUMNS($A:A),RAW!$DY2:$IT2,0))
copied across and down

The formula on the summary page can be developed to accomodate the companies not being in the same order, but if they always will be this is unnecessary

[analyst1]

Yes, the companies should always match.