Analyse Data Table

Ittinger

New Member
Joined
Feb 17, 2016
Messages
3
Hello all

I am working on analysing accident data listed in a table, not a range (Excel 2010 Win7). The data get imported (VBA) to that table from single sheets with which accidents are recorded. Where for example an injury is ticked, the corresponding cell contains a boolean true/false value. The new data get added as a row to the table.

Here is a simplified example of the table:

IP First NameAccident DateOcc AccidentNon Occ AccidentHeadTorsoUpper ExtremitiesLower ExtremitiesSeverityDays off
Schulz08.01.2015TRUEFALSEFALSEFALSETRUEFALSE10
Kevin12.01.2015TRUEFALSEFALSEFALSETRUEFALSE11
Drazan28.01.2015TRUEFALSEFALSEFALSEFALSETRUE223
Reto30.01.2015TRUEFALSEFALSETRUEFALSEFALSE15
Antonio30.01.2015TRUEFALSETRUEFALSEFALSEFALSE4360
Rüedi02.02.2015TRUEFALSEFALSEFALSETRUEFALSE226
Gianni24.02.2015TRUEFALSEFALSEFALSEFALSETRUE12
Roland01.01.2016TRUEFALSETRUEFALSEFALSEFALSE210
Salvatore01.01.2016TRUEFALSEFALSETRUEFALSEFALSE337
Pietro11.01.2016TRUEFALSEFALSEFALSETRUEFALSE13
Marco19.01.2016TRUEFALSEFALSEFALSETRUEFALSE12
Fabio06.02.2016FALSETRUETRUEFALSEFALSEFALSE3#N/A
Philipp08.02.2016FALSETRUEFALSEFALSEFALSETRUE210

<tbody>
</tbody>

I want to consolidate the data to a list from which the numbers for each category and the sum of lost days is listed by month. See example based on the data above:

20152016
JanFebJanFeb
Occ Accident5240
Non Occ Accident0002
Head1011
Torso1010
Upper Extremities2120
Lower Extremities1101
Days off389285210

<tbody>
</tbody>


I was able do a first step using a pivot table for one item only. As soon as I added a second category, the pivot combined the two lines.


-- removed inline image ---



-- removed inline image ---



Is there a way to configure the pivot such as to get something like my sample table above?
Alternatively, can someone help me in coming up with an other way to solve my problem?

Appreciate any help.

Best regards from Switzerland.

Mike
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Noodleski

Active Member
Joined
Nov 18, 2015
Messages
467
Hi Mike,
with a pivot, it would be very difficult to achieve this, since your base data isn't structured in a pivot-friendly way.
Assuming you wouldn't want to restructure the base data (since it's related to VBA and source files) I'd go with a (less flexible) formula solution of countifs and sumifs.
Alternatively you could also add equip your pivot with a filter/slicer and cycle through the different injury categories.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,186
Messages
5,640,688
Members
417,161
Latest member
Devon150

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
Top