Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting by sector and find largest 3 number each macro help needed
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 | +-------------------------------------------------------------------+ -- barkiny ------------------------------------------------------------------------ barkiny's Profile: http://www.excelforum.com/member.php...o&userid=20397 View this thread: http://www.excelforum.com/showthread...hreadid=396461 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting by sector and find largest 3 number each macro help needed
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. -- HTH... Jim Thomlinson "barkiny" wrote: 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 | +-------------------------------------------------------------------+ -- barkiny ------------------------------------------------------------------------ barkiny's Profile: http://www.excelforum.com/member.php...o&userid=20397 View this thread: http://www.excelforum.com/showthread...hreadid=396461 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting by sector and find largest 3 number each macro help needed
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 -- barkiny ------------------------------------------------------------------------ barkiny's Profile: http://www.excelforum.com/member.php...o&userid=20397 View this thread: http://www.excelforum.com/showthread...hreadid=396461 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting by sector and find largest 3 number each macro help ne
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 DataSort 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 "barkiny" wrote: 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 -- barkiny ------------------------------------------------------------------------ barkiny's Profile: http://www.excelforum.com/member.php...o&userid=20397 View this thread: http://www.excelforum.com/showthread...hreadid=396461 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting and coloring cells, formula or macro help needed | Excel Discussion (Misc queries) | |||
Formula or macro needed for sorting complex data issue. | Excel Discussion (Misc queries) | |||
find the largest number in column | Excel Discussion (Misc queries) | |||
Find the largest number | Excel Programming | |||
macro needed for sorting txt in a ceratian manner | Excel Programming |