Archive: Automate Pivot Tables and Create Graphs in Excel
Budget
108$
per month
Posted: 5 years ago
Closed
- Description
- Project 1
I have an excel spreadsheet with one worksheet - sheet1 - that I get daily on email that has around 20000 rows of data
which is my source file
It has a column called Queue Age which is a numeric value
It also has a column called Work Type which is abbreviated
In another worksheet - Sheet2 - I can give the description for this abbreviation and the category that
the description belong to. In other words 3 reference columns - Work Type, Description, Category
The requirement is to build an excel template as follows:
In a Summary worksheet - Sheet3 - I want to pivot the 20000 rows by category
In the same sheet I want to pivot the categories broken into Queue Age groups 1-5, 6-10, 11-15, 16-20 and 20+
Next to the volume on Queue Age group on this pivot I want to see the percentage for that age group in that category
The number of pivots on Sheet3 will vary daily depending on the amount of Work Types in the source file.
I want to automate this process so that when I get the file I can open it and plug the data into the temlplate that already
has the reference data in another tab and at the click of a refresh button I want to update this Summary sheet - Sheet3.
This can be done using VBA or Macros or any other way in Excel. However, I need to know how I can change it if I add/delete
categories on the reference data tab or decide to add/delete/change the age brackets etc.,..
All I need to know is where I need to go in the script to change it.
Project 2
I have two excel spreadsheet with one worksheet - sheet1 - that I get daily on email that has around 50000 rows of data
which is my source file
Both files have similar data but the difference is the system name in column A which has either CM or Js as the System
Each file has two columns called start and end date(dd/mm/yyyy hh:mm:ss) and a column called work type which is abbreviated
There are only two work types in both files called Notify and Claim
First I want to split the first file into several tabs based on the system, month and the work type
So each new sheet will have
JS January Notify,
CM January Notify,
JS January Claim,
CM January Claim,
JS February Notify,
JS February Claim and so on
In a Summary tab, I want to show the volume of cases each month and the average days to complete a case on the
same month and the average days to complete a case over a reporting period, Jan to Dec
In other words, when this split happens in Sheet2, the end date would have been broken into the month name January
in a new column and the end date minus the start date would give the days to complete that case in another column for each system tab
This should happen for all the other tabs.
The requirement then is to build a graph based on the above data
The idea of the graph is so that the user can see what the base line was for the 12 months for each system and work type
and how each month performed based on the volumes received
It is used to compare how each work type is performing against the yearly average of one system against the other
For example, from Jan to Dec, I want to show the volumes for each month for each system
In the same graph I want to show the average days to complete a case in Jan, Feb, Mar up to Dec by system by work type
In the same graph I want to show the average across 12 months in a single line for two work type
I doubt if this is possible in Excel graphs.
I don't mind having two graphs either by system or work type provided it is smart and easier to read and understand.
I will provide the example data for the right individual to work with for both projects...
Skills:
automation,business support,spreadsheets,VBA programming language
- Category
Similar freelance jobs
$430
I'm looking for someone who's available to start ASAP on some spreadsheets. The data is my customers data and must be handled with care. I will provide the requirements/tasks to anyone who's interested. Please get in touch ASAP. Skills:....
4 year ago
$403
Wife & I were falsely accused of stealing by a major department store, three security guards followed me us to the railway station, they surrounded us in front of the general public who saw everything that was going on, blackmailed us, never apologised when they did not find anything on us: So, basically......
4 year ago
$66
We work with 35 people with Office365. we have MS sharepoint and are going to migrate to MS teams. who can help us with implementing the rights and different project folders. Not a big project. takes an estimated 2-3 days. Can be a intro to future projects. Looking forward! English needs to be good.......
4 year ago
$71
I would like to buy 1000 leads for 15$ targeting UK women aged 20-55, wanting weight loss, a personal trainer or wanting fitness videos for home training. I would like to test the leads first and if they are of good quality i can promise to buy much more. You also offer a script writing of a telesales......
4 year ago
$335
Looking for an expert in structural equation modelling and SPSS. I have a paper in SEM would like your assistance with, The job requires someone with strong knowledge of IBM AMOS and model building. Skills:....
4 year ago
$335
I need a local (UK-based) online and telephone research assistant who can find me the contact details of the key decision makers in the largest 100 nursery groups in England. This will take a mix of online and real world (phoning) sleuthing to identify the right senior level contacts. Although a short......
4 year ago
$128
Having Custom packaging Kraft boxes created for your products can be insanely easy, if you are careful with your choices. Therefore, it’s best that you bring into account some important factors that will allow you to make the right decision. https://custompackagingpro. com/. Skills:....
4 year ago
$216
I have a draft agreement from my potential business partner that needs reviewing. Its is in regards to entering a partnership concerning a new restaurant. I need someone suitably qualified to ensure the details of the contract do not compromise my situation. Skills:....
4 year ago
$168
I am a self employed person. I like to submit my tax return. I have all my records of expense and earning, working as a Courier Driver i already made an excel sheet. kindly let me know how much you will charge for submitting to HMRC, and how much time you require to complete the job Regards Khan. Skills:....
4 year ago
$25
I am looking for qualified primary teachers, with experience in phonics, particularly if you are an english literacy subject leader/coordinator OR Are you in charge of choosing phonics systems for schools, to watch a short 7 minute presentation on my books and tell me how much you think they are worth......
4 year ago
$35
You will have at-least 5 years of experience in: - Carrying out Party wall services in the residential property market - Draft Party wall documents - Preparing and issuing fee proposals and negotiating party wall awards - Business development experience preferred - Client-facing with a positive demeanour......
4 year ago
$168
I need a spreadsheet that's been created tidying up all images same size box's made to look neat. Skills:....
4 year ago
$230
I am marketing an employment agency. The agency is owned by the employees and as such can give different types of payments such as dividends. This means there is different tax treatment. I need someone, who knows Excel well, do build a spreadsheet for me where I can put in a day rate for a contractor......
4 year ago
$13
I am looking for freelancers who can help me with a quick task on eBay. It will takes 2 minutes maximum. You MUST have your own eBay account that is already established - it cannot be newly created. If you have an existing eBay account and want to get paid to do 2 minutes work please get in touch for......
4 year ago
$226
We oparating a Quartz mine in Sri Lanka which has a purity of 99+. We are looking for a bulk buyer and the appreciate set of machinry for crushing into micron sizes as a investment. You can bring investment here and there is no duty will be charged. Also you can bring back the profits you earned by exporting......
4 year ago
$11
I need tax advice on a property transaction. 1) Three of us own a freehold of a property that has been split into three independent flats. We would like to take out a BTL mortgage and have been advised that we should create a lease for each flat. I would like some advice regarding our options and potential......
4 year ago
$84
We currently have a seller central account on Amazon. We require some support with this from someone with experience with Amazon specifically creating and optimising listings. We currently have approx 12 products listed within our seller central. We are aiming to have this completed by mid February at......
4 year ago
$204
I need telesales women to call companies in the united kingdom and speak to key divisions makers. You will be selling our recruitment services. Skills:....
4 year ago
$844
I wanted to find about the hours and cost of running the following project: - Salesforce contacts database is approx 19k of which 17k contacts need to be demoted into a lead record. this requires extracting and re-uploaded to salesforce. - along with this the related information such tasks, activities......
4 year ago
At acework (www. acework. io), we are fierce advocates for remote work. Our online platform is built exclusively for matching remote-ready candidates with vetted remote work. We are looking for a Talent and Sales Manager with experience in hiring for profiles and selling our platform to global talent......
4 year ago
$220
Please provide 4-5 detailed slides for macro assessment of local demand and market needs for Modular Homes in CENTRAL Asia. For example, demand and supply of shipping containers (accumulating and availability). It is important for business feasibility calculations and comparison with modular homes made......
4 year ago