Excel Pivot help

mtthompsons
mtthompsons used Ask the Experts™
on
Hi all,

From last few hrs i have been trying to make a Pivot table, Column "C" "Task" based Person based Count.

Wanted the work done by
How many Voice, Editing, Camera, Anchor work did "Micheal" do

If anyone can help me would be great
attached sample file
Thanks in advance
Sample.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NorieAnalyst Assistant

Commented:
Your data just isn't in the right format for producing a pivot table like that.

What you should have are columns for Date, Work Type, Person and Task.

With that set up you could easily produce the pivot table you describe, and even take it further by grouping by Date, filtering by Work Type etc.

If you have a version of Excel that supports Power Query/Get & Transform Data... you could easily change the layout of the data to make it conducive to the creation of a pivot table.

Author

Commented:
Thank you, can you help me with a sample excel please
NorieAnalyst Assistant

Commented:
Not sure what you mean, what do you want a sample of?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
You said
Your data just isn't in the right format for producing a pivot table like that.

What you should have are columns for Date, Work Type, Person and Task.

But those data are as Columns, so i am not sure how to change them
NorieAnalyst Assistant

Commented:
You have 4 columns for Work Type, you should only have one, see the attached file.
Sample-Reformatted.xlsm

Author

Commented:
Thanks, but its one task which multiple people do, adding them 3 or 4 times for one task would be tough, any other ways please?

Author

Commented:
Example
A Video is produced
1 Person Shoots it
2nd person edits it
3rd person gives a voice over
4th may do graphics
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
Something like attached may help you...
PivotReformatted.xlsx

Author

Commented:
Shums report is as i wanted but as mentioned above is it only way that i add same entries multiple times to achieve this?
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
Yes,

As mentioned by Norie, your sheet needs formatting to achieve what you are looking for.

Author

Commented:
So each entry we need to place multiple times?
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
Unfortunately Yes :)
Hi,
In my opinion there are two another possible approaches to solve the issue :
1- Formulas to count the data (Check the attached file FormulaWork Sheet).
2- If you are using version that has "Power Query" you can use it to reformat the data to tabular data (Check the attached file PowerQuery Sheet).

Br,
Abbas Abdulla
Sample.xlsx

Author

Commented:
abbas abdulla superb thanks,
Can i know steps to do it and i have office 2007 and 3013
IF you have 2013 excel - see this video to have idea about the steps done https://youtu.be/vztnlZhHzos

Author

Commented:
Thanks, data from Table option is not found in my excel, i have 2013 professional
You should download power query as addin from Microsoft website just google it

Author

Commented:
Thanks a lot

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial