Excel Pivot help

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
mtthompsonsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
mtthompsonsAuthor 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?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

mtthompsonsAuthor 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
mtthompsonsAuthor 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?
mtthompsonsAuthor Commented:
Example
A Video is produced
1 Person Shoots it
2nd person edits it
3rd person gives a voice over
4th may do graphics
ShumsExcel & VBA ExpertCommented:
Something like attached may help you...
PivotReformatted.xlsx
mtthompsonsAuthor 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?
ShumsExcel & VBA ExpertCommented:
Yes,

As mentioned by Norie, your sheet needs formatting to achieve what you are looking for.
mtthompsonsAuthor Commented:
So each entry we need to place multiple times?
ShumsExcel & VBA ExpertCommented:
Unfortunately Yes :)
abbas abdullaCommented:
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
mtthompsonsAuthor Commented:
abbas abdulla superb thanks,
Can i know steps to do it and i have office 2007 and 3013
abbas abdullaCommented:
IF you have 2013 excel - see this video to have idea about the steps done https://youtu.be/vztnlZhHzos
mtthompsonsAuthor Commented:
Thanks, data from Table option is not found in my excel, i have 2013 professional
abbas abdullaCommented:
You should download power query as addin from Microsoft website just google it

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mtthompsonsAuthor Commented:
Thanks a lot
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.