create a top 10 list of sales figures

[Guest]

Help required to solve a pain of an issue (and save what little hair I have
left)

I have a very large worksheet containing monthly sales data for 600+
products sorted by product group. I want to create a series of "top 10's"
for the data.

The data is set out a bit like this:

Item code Name Price Unit Sales(1 column per month)

There are columns in between some of these but they aren't really relevant
to what I want to achieve.

I need to create a conditional formula that will examine the sales figures
by month and product group and pull out the highest sellers for each
group/month (complete with all the indentifying data). I also need to look
at sales figures over the entire range and pull out overall top sellers.

All the functions I have looked at will give me the overall position of a
sales figure but won't tie it to the name or item code and they will not
break the data down by group.

Any suggestions gratefully received......the boss is starting to get
impatient!!!!

 

[Guest]

Dave,

Use a PivotalTable to summarize the data em create your top 10 lists.

 

[Guest]

Sorry. I should have pointed out that I was trying to achieve this without
having to create pivot tables.....but thanks for the suggestion.

 

[Guest]

Davidy,

That will be, in my opinion, your best bet. You can easily summarize the
data and once that it is done, simply right-click on the row field and select
the option Field Setting and the click on Advanced. There you will have the
option to list the top 10, top 15, top 20, top 5, or literally anything
really.

And all that wil only a few clicks and now messy formulas.

 

[macropod]

hi Dave,

Suppose your data start in row 2 (row 1 is for headings) and you want an
ordered list of the 10 items from Column B that correspond to the 10 highest
values in column I. And suppose too that the output is also to start on row
2. In that case, the formula:
=INDEX(B:B,MATCH(LARGE(I:I,ROW()-2),I:I,0))
input in any available column on row 2 and copied down to row 11 would work,
PROVIDED, the values in column I are unique. If any values are repeated, the
formula will return duplicates from column B for the column I matches. If
there is the risk that values in column I are repeated, you could use the
above for the first row and, for the third row (copied down to row 11):
=IF(LARGE(I:I,ROW()-2)=LARGE(I:I,ROW()-3),INDEX(B:B,MATCH(LARGE(I:I,ROW()-2)
,OFFSET(I,MATCH(K3,B:B,0),0,ROW(I)-MATCH(K3,B:B,0),1),0)+MATCH(K3,B:B,0)),IN
DEX(B:B,MATCH(LARGE(I:I,ROW()-2),I:I,0)))

If your output table starts on any row other than row 2, simply change the
'-2' references in the above formulae to match the starting row counts.

Cheers