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?
 
abbas abdullaConnect With a Mentor Commented:
You should download power query as addin from Microsoft website just google it
0
 
NorieVBA ExpertCommented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
NorieVBA ExpertCommented:
Not sure what you mean, what do you want a sample of?
0
 
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
 
NorieVBA ExpertCommented:
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
 
mtthompsonsAuthor Commented:
Thanks a lot
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.