sorting by sector and find largest 3 number each macro help needed

[barkiny]

there are 3 columns
1: name of the company
2: the sector it belongs (food,beverage...)
3: net sales


i want to find out the largest or smallest (doesnt matter) 3 (or 5
whatever) net sales of each sector ( and hide all other rows) (in
ascending or descending format)

is it be so difficult to write a macro like that)


+-------------------------------------------------------------------+
|Filename: data.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3698 |
+-------------------------------------------------------------------+

 

[Jim Thomlinson]

I would avoid the Macro and just use a Pivot Table. Select the Data and
choose Data -> Pivot Table. Follow the wizard. Place the company and the
sector in the left hand column. Place the sales in the middle. Right click on
the sector and choose Field Settings -> Advanced and modify the Auto Show
options to show only the top 3.

 

[barkiny]

i dont want to use pivot table because there are many other search
criterias based on that dataset and it wont be practical for other
users or when the data range changes over time

it has to be vba code

thanx in advance

 

[Vacation's Over]

lots of ways to aproach this. BEST way depends on how it will be used, user
abilities, do you need choices to asc/desc 3 or 5 shown and so on.

For starters Data>Sort by sector then by net sales will give you the full
sorted list.(asc / Dsc choice is yours)

Then you could throw in an if formula in the next column so if sector
matches the previous row add one else start again at 1

say - D3 =If(B3 = B2,D2 + 1,1)
So now you have a column that shows the running ranking of each sector and
you can get a macro to hide rows where column D is greater than 3 - for the
top 3 list
if sorted desc. & bottom 3 if sorted asc.

Do not include D column in the sorting

hope this helps