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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.
1
mtthompsonsAuthor Commented:
Thank you, can you help me with a sample excel please
0
NorieAnalyst Assistant Commented:
Not sure what you mean, what do you want a sample of?
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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
0
NorieAnalyst Assistant Commented:
You have 4 columns for Work Type, you should only have one, see the attached file.
Sample-Reformatted.xlsm
0
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?
0
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
0
ShumsDistinguished Expert - 2017Commented:
Something like attached may help you...
PivotReformatted.xlsx
0
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?
0
ShumsDistinguished Expert - 2017Commented:
Yes,

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

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
0
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.