Helping Streamline file which automatically filters, populates and then exports

[Testerson]

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

[FlameRetired]

Hi again Testerson. Welcome to the forum.

That is a very involved description.

We are going to need an Excel workbook upload the is representative of what you are working with and toward. Think BEFORE and AFTER.

If you are not familiar with how to do this (the paperclip icon doesn't work ):

To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

• be sure to desensitize the data
• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.