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