Closed Thread
Results 1 to 2 of 2

Creating file which automatically filters, populates and then exports

  1. #1
    Registered User
    Join Date
    08-29-2018
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    2

    Creating file which automatically filters, populates and then exports

    Hi there,

    I'm trying to deal with an issue which I am dealing with currently in upgrading a currently used system which I am doing for Excel.

    I know that it can become faster but have no real idea how.

    I have a broad range of data that I import weekly in the form of a spreadsheet in order to extract certain elements of it to convert to a more condensed easily readable table.

    When the data is imported out of the 15 included columns, five of them are the ones that I am primarily using/using for reference. Which are the 'status', 'user score', 'type', 'quarter' and 'name'.

    The last column is a status field with options such as 'completed, nearly and not started' options to choose from. There is also a column with a 'user score' field, which has entries of 0-13 in it when filled. When the data is imported, I would first like for there to be a way to automatically filter the data so that only those rows with a 'nearly' status are displayed. Of the data in the rows that shows up, some of the 'user score' columns have empty entries, for all of those who have this I would like them to be filled with the number '11'.

    On a separate tab within the spreadsheet there is a PivotTable table which finds the takes the 'user score' for each 'name' and finds the average for it based on the filter for the table. The filter on the PivotTable table decides which quarter is being shown/averaged. The table then displays the user scores averaged for a single quarter or over multiple quarters. Currently I am using a set designed Pivottable table and using the 'change data source' button in the PivotTable options tab to link it to the new source data whenever I need to update it.

    For Example:

    This would be the original import data -

    name type user score quarter status
    a dog 7 4 Completed
    b dog 8 4 Completed
    c cat 3 4 Completed
    d ghost 9 4 Completed
    e ghost 2 4 Completed
    f dog 6 4 Completed
    g cat 5 4 Completed
    h fish 4 3 Completed
    i bird 2 3 Completed
    j dog 7 1 Completed
    k fish 8 2 Completed
    l bird 4 Completed
    m cat 3 Completed
    n ghost 2 Completed
    o ghost 3 Completed
    p ghost 2 Planned
    q cat 4 Planned
    r fish 2 Planned
    s bird 4 Planned
    t bird 1 Planned
    u dog 4 Planned
    v fish 1 Planned
    x cat 2 Planned
    y ghost 2 Planned
    z dog 4 Planned

    -Then the status column would be filtered so that it where 'Planned' is in the status section, the 'user score' is set to 11.

    -The resulting data (not just that which has been filtered but all of it) would be imported into the PowerPivot Table into the corresponding format when the quarter filter is set to display just quarter 4 averages:

    Quarter ------- 4 (drop down arrow here for selection of quarter)

    a b c d e f g h i j k l m n o

    Dog 7 8 6 7

    Cat 3 5 11

    Bird 2 11

    Ghost 9 2 11 11

    Fish 4 8


    I was wondering if there was a better way to streamline this process or
    complete it in a better or faster way?

    I was wondering if there was also a way to export the table in a visually pleasing format? As of current I am simply colour coding the different scores and putting them into a new table, but I am sure that there is a different way?

    TL;DR
    Using a long winded way of doing things
    Copying data multiple times
    Manual work and input
    I would like to find a faster way of doing this and find a nicer way to display this.

    Sorry that this post was long and possibly nonsensical, just looking for a bit of help !

    Also, please do say if anything needs explaining a little more.

    EDIT: The formatting on the tables hasn't worked twice while I was trying to arrange it in a not-so-horrible-to-look-at-way, so apologies for that.

    Thanks
    Last edited by Testerson; 08-29-2018 at 08:20 AM. Reason: Formatting

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    16,324

    Re: Creating file which automatically filters, populates and then exports

    Welcome to the Forum, unfortunately this is a duplicate post and as such will now be closed, you may continue in your other thread.

    Thread Closed.

Closed Thread

Thread Information

Users Browsing this Thread

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

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