My Assistant
Site Message
(Message will auto close in 2 seconds)
  |
 Top 10 Autofilter (maybe) question
|
|
 Dec 1 2004, 11:44 AM
Post
#1
|
|
|
UtterAccess Addict Posts: 294 From: Washington State, USA  |
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. |
 |
 
|
|
Dec 1 2004, 02:41 PM
Post
#2
|
|
|
UtterAccess VIP Posts: 3,667 From: Toronto, ON |
is working with numeric values, or Dates - it looks is not working with Text.
|
|
|
|
|
Dec 1 2004, 03:18 PM
Post
#3
|
|
|
UtterAccess Addict Posts: 294 From: Washington State, USA |
Would there be any other function you can think of could be substituted.
|
|
|
|
|
Dec 1 2004, 03:47 PM
Post
#4
|
|
|
Retired Moderator Posts: 10,959 From: Prague,CZ / Kiev,UA |
Hello,
how do you decide which product belongs to 'Top 10'? |
|
|
|
|
Dec 1 2004, 04:17 PM
Post
#5
|
|
|
UtterAccess Addict Posts: 294 From: Washington State, USA |
Whenever a product is returned, the name is added to column b.
|
|
|
|
|
Dec 1 2004, 04:20 PM
Post
#6
|
|
|
Retired Moderator Posts: 10,959 From: Prague,CZ / Kiev,UA |
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. |
|
|
|
|
Dec 1 2004, 04:34 PM
Post
#7
|
|
|
UtterAccess Addict Posts: 294 From: Washington State, USA |
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? |
|
|
|
|
Dec 1 2004, 04:57 PM
Post
#8
|
|
|
Retired Moderator Posts: 10,959 From: Prague,CZ / Kiev,UA |
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. I used 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. (IMG:http://www.utteraccess.com/forum/style_emoticons/default/frown.gif) martin |
|
|
|
|
Dec 1 2004, 05:22 PM
Post
#9
|
|
|
UtterAccess Addict Posts: 294 From: Washington State, USA |
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%.
I appreciate all your time (IMG:http://www.utteraccess.com/forum/style_emoticons/default/frown.gif) Big hugs... Michelle |
|
|
|
|
Dec 2 2004, 06:08 PM
Post
#10
|
|
|
UtterAccess Addict Posts: 294 From: Washington State, USA |
Can you tell me how to change it from Top 5 to Top 10, thanks so much (IMG:http://www.utteraccess.com/forum/style_emoticons/default/frown.gif) - Michelle
|
|
|
|
|
Dec 2 2004, 08:03 PM
Post
#11
|
|
|
UtterAccess VIP Posts: 3,667 From: Toronto, ON |
Select Field Settings (PivotTable Toolbar) and click on Advanced > you'll see 'Top" settings.
|
|
|
|
|
Dec 3 2004, 04:26 PM
Post
#12
|
|
|
UtterAccess Addict Posts: 294 From: Washington State, USA |
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 (IMG:http://www.utteraccess.com/forum/style_emoticons/default/frown.gif)
Here is the revised attached. |
|
|
|
|
Dec 3 2004, 04:39 PM
Post
#13
|
|
|
Retired Moderator Posts: 10,959 From: Prague,CZ / Kiev,UA |
Hello again Michelle,
in my attachement: Right-click any cell in the range D3:D7 (fields), select Field Settings from the menu => Advanced, change Top to 10 M. |
|
|
|
 |
|
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 25th May 2013 - 10:44 PM |