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



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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

earthworm
  • Question Question
Autofit table in Outlook upon copying from Excel sheet using Macro
Replies
1
Views
130
KOKOSEK
KOKOSEK
R
  • Question Question
VBA to add to data model when creating Power Query tables`
Replies
3
Views
492
Raddle
R
J
  • Question Question
How to clean scrape a website for financial data?
Replies
9
Views
445
jonathanwang003
J
E
  • Locked
  • Question Question
Excel formjlailter data to be analysed
Replies
1
Views
141
Fluff
Fluff
D
  • Solved
VBA to paste table into Outlook
Replies
5
Views
621
Dani_LobP
D

Forum statistics

Threads
1,222,900
Messages
6,168,926
Members
452,227
Latest member
sam1121

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