Pivot Table structure for 'instances'

5thMarReg

New Member
Joined
Mar 23, 2013
Messages
13
I haven't worked with pivot tables since Excel97 (?) so aside from losing my feel for how to solve this issue, I'm also not sure if I had ever done anything like this before.
Here's the issue...

This is an abbreviated mockup of the data:
(sorry, I can't post attachments)

GroupQ1Q2Q3Q4
A4231
B2320
C0144
D3402
E1013

<tbody>
</tbody>

The result that I am seeking is to determine the number of instances for each of the numbers in the columns.

For instance, Group C has:
1 instance of 0's
1 instance of 1's
2 instances of 4's

Visually it should look like this...

Option 14's3's2's1's0's
A11110
B01201
C20011
D11101
E01021

<tbody>
</tbody>

Or like this...

Option 2ABCDE
4's10210
3's11011
2's12010
1's10102
0's01111

<tbody>
</tbody>


I am able to accomplish this using COUNTIF, however the dataset is actually very large and I was hoping that it could be done using a pivot table instead. I played around with many different ways of positioning the headings, but I can't seem to get the result that I am looking for - or determine if it is even possible (although I assume that it is - which is why I am here asking for guidance).

Could I ask for someone to steer me towards the needed solution?
(btw, I am currently using Excel 2010)
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top