Analyse Data Table

I

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:

[TABLE="width: 692"]
<tbody>[TR]
[TD]IP First Name[/TD]
[TD]Accident Date[/TD]
[TD]Occ Accident[/TD]
[TD]Non Occ Accident[/TD]
[TD]Head[/TD]
[TD]Torso[/TD]
[TD]Upper Extremities[/TD]
[TD]Lower Extremities[/TD]
[TD]Severity[/TD]
[TD]Days off[/TD]
[/TR]
[TR]
[TD]Schulz[/TD]
[TD]08.01.2015[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Kevin[/TD]
[TD]12.01.2015[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Drazan[/TD]
[TD]28.01.2015[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]Reto[/TD]
[TD]30.01.2015[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Antonio[/TD]
[TD]30.01.2015[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]360[/TD]
[/TR]
[TR]
[TD]Rüedi[/TD]
[TD]02.02.2015[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]26[/TD]
[/TR]
[TR]
[TD]Gianni[/TD]
[TD]24.02.2015[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Roland[/TD]
[TD]01.01.2016[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Salvatore[/TD]
[TD]01.01.2016[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]37[/TD]
[/TR]
[TR]
[TD]Pietro[/TD]
[TD]11.01.2016[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Marco[/TD]
[TD]19.01.2016[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Fabio[/TD]
[TD]06.02.2016[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]3[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Philipp[/TD]
[TD]08.02.2016[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="width: 436"]
<tbody>[TR]
[TD][/TD]
[TD="align: right"]2015[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]Jan[/TD]
[TD="align: right"]Feb[/TD]
[TD="align: right"]…[/TD]
[TD="align: right"]Jan[/TD]
[TD="align: right"]Feb[/TD]
[/TR]
[TR]
[TD]Occ Accident[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Non Occ Accident[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Head[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Torso[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Upper Extremities[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Lower Extremities[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Days off[/TD]
[TD="align: right"]389[/TD]
[TD="align: right"]28[/TD]
[TD][/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]


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
 
Sort by date Sort by votes
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Help with Pivot Table Macro
Replies
0
Views
194
SCPbrito
S
S
  • Solved
Having trouble with pivot table macro
2
Replies
12
Views
771
RoryA
RoryA
J
  • Solved
VBA/Macro: Adding Pivot table with connection string
Replies
1
Views
495
Jette3173
J
R
  • Question Question
VBA to add to data model when creating Power Query tables`
Replies
3
Views
852
Raddle
R
H
  • Solved
Macro to Filter Pivot Table
Replies
2
Views
1K
howard
H

Share this page

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