+ Reply to Thread
Results 1 to 8 of 8

Running 2 complex filters and copying them to a new sheet

  1. #1
    Registered User
    Join Date
    01-12-2011
    Location
    Egham, PRC, England
    MS-Off Ver
    Excel 2003
    Posts
    8

    Running 2 complex filters and copying them to a new sheet

    Hello everybody! Normally I get some good responses on here so hopefully you will be able to help me again.

    I have a spreadsheet, a simple sales tool.

    UPC Description Department Sales last Week Sales this Week
    00001 Item 1 Department 1 100 200
    00002 Item 2 Department 1 200 400

    and repeat with 4000 UPCs and 35 departments with just as much sales data.

    Being the company we are and being a startup, I have been employed with analysing the sales data every week.

    My manager has asked for a "Top 5 by Department" list so ideally I was hoping to filter by department, then run the Excel 2007 "top 10" list for each department thus giving me the data I needed. WRONG was I. I can only seem to run the top 10 for the entire store, not for each department. This is currently taking me 2 hours a week to filter by each department, then put them in number order, copy and paste the top 5 into a table and email it out. I need this scripted somehow, but I'm coming to a dead end.

    I have no code to show, as I have literally got to the point of just deleting it all and starting it all over again.

    So could you please help me out with this? It would be muchly appreciated.

    If I have missed something simple, like putting it into a pivot table then please tell me and I will let you know.

    Also, the computers are fairly slow, the less calculations Excel needs to do the better, as I remember running a script with 20,000 calculations in and it slowed us all down :-(

    Kindly thanking you all in advance

    Chris

    One other thing; a quick one: The "Department" column is generated manually, I have a back copy of the catalogue which references the UPC from another sheet and runs a VLOOKUP to return the department code. However I would like to add a button to a userform which I can just click and it inserts a column and the required formula. That would also help me out!

  2. #2
    Registered User
    Join Date
    01-12-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Running 2 complex filters and copying them to a new sheet

    have you tried a pivot table and then a top ten filter that way? That might be easier than doing VLOOKUPS.

  3. #3
    Registered User
    Join Date
    01-12-2011
    Location
    Egham, PRC, England
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Running 2 complex filters and copying them to a new sheet

    Yes I have, but this only seems to allow me to filter out the top 5 departments now, not by UPC level :-(

    I need to know which are the top 5 best sellers for each department

  4. #4
    Registered User
    Join Date
    01-12-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Running 2 complex filters and copying them to a new sheet

    Are you sure you are using the pivot table correctly? I have attached an example. I added a calculated field that summed the total sales, and then did a top ten of "UPC" based on that. For this example, I only used top 5. I apologize if I am completely misunderstanding your post. Maybe it is a version issue as I do have 2010.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-12-2011
    Location
    Egham, PRC, England
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Running 2 complex filters and copying them to a new sheet

    Got it applied and it worked. Thank you!

    Due to the way that I need it diplayed, its showing as Description then the UPC on the line underneath (as it needs to be human readable at a glance). Is there no way to make the pivot table look like the original one? (i.e. UPC far left column, column 2 description etc)

  6. #6
    Registered User
    Join Date
    01-12-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Running 2 complex filters and copying them to a new sheet

    Click anywhere on the Pivot Table, then in the ribbon, click the "PivotTable Tools" "Design" tab. You will see set of buttons called "Layout". Play with those until you find what you like. Personally I find "Tabular View" the most readable. To change the position of the "Description" and "UPC" fields, move them up and down in the "Row Labels" as desired. If that's not clear i can upload another example.

  7. #7
    Registered User
    Join Date
    01-12-2011
    Location
    Egham, PRC, England
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Running 2 complex filters and copying them to a new sheet

    Got it! Thank you ever so much!! I can sleep now Now I have got the hang of it, the next process is just to design the workbook to fit the three new store openings, timely moreso than hard. I owe you one!

  8. #8
    Registered User
    Join Date
    01-12-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Running 2 complex filters and copying them to a new sheet

    You're welcome. A couple other pro tips: 1. Format your data as a table, and make sure that the "Source Data" for the Pivot Table is the Table with your data.
    2. after you add new data to the table, hit refresh on the Pivot Table under the "options" tab. It will automatically update.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel 2007 : Copying data filters from one sheet to another?
    By rsidheshkumar in forum Excel General
    Replies: 1
    Last Post: 05-02-2010, 03:15 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1