Full Version: Top 10 Autofilter (maybe) question
UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel
MDB
I have a small list of about 30 items. With the date in column a, and in column b the product names.
Every time a product is returned, the product name is added to a column (so problem products will appear more often on the list). I want to see what the top 10 products are that are being returned, I tried the Top 10 in the Autofilter but it didn't seem to work with words. thanks for any assistance you can give me.
xteam
is working with numeric values, or Dates - it looks is not working with Text.
MDB
Would there be any other function you can think of could be substituted.
KingMartin
Hello,
How do you decide which product belongs to 'Top 10'?
MDB
Whenever a product is returned, the name is added to column b.
KingMartin
Yes, but which criteria should be fulfilled so that the product is a member of 'TOP 10' group?
Is it e.g.
a) first ten names alphabetically sorted or
b) top 10 occurences?
M.
MDB
The Top 10 occurences of the product name.
Yes, but which criteria should be fulfilled so that the product is a member of 'TOP 10' group?
Is it e.g.
a) first ten names alphabetically sorted or
b) top 10 occurences?
KingMartin
OK,
One possibility would be to use Pivot Table.
product is dragged twice into the table, once as row header, second time as data (aggregate function should be automatically set to Count).
Right-click any field in the pivot table's product column, Field Settings=>Advanced, AutoShow Automatic, TOP 10, Use Count Of product.
Oused TOP 5 as using 10 will show all the products in your sample sheet (as there are many represented by 1 occurrence).
See the attachment.
frown.gif
martin
MDB
Pivot tables have always whoop'd my behind.. they are hard.. but I was able to see mostly what you did. But I couldn't figure out how to change it from 5% to 10%.
appreciate all your time frown.gif Big hugs... Michelle
MDB
Can you tell me how to change it from Top 5 to Top 10, thanks so much frown.gif - Michelle
xteam
Select Field Settings (PivotTable Toolbar) and click on Advanced > you'll see 'Top" settings.
MDB
I have Excel 2000, I didn't see advanced under the Field Settings, do you think it is still possible to edit it to change it to Top 10? Thanks frown.gif
Here is the revised attached.
KingMartin
Hello again Michelle,
On my attachement:
Right-click any cell in the range D3:D7 (fields), select Field Settings from the menu => Advanced, change Top to 10
M.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.