Analyse Data Table

[Ittinger]

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 Name	Accident Date	Occ Accident	Non Occ Accident	Head	Torso	Upper Extremities	Lower Extremities	Severity	Days off
Schulz	08.01.2015	TRUE	FALSE	FALSE	FALSE	TRUE	FALSE	1	0
Kevin	12.01.2015	TRUE	FALSE	FALSE	FALSE	TRUE	FALSE	1	1
Drazan	28.01.2015	TRUE	FALSE	FALSE	FALSE	FALSE	TRUE	2	23
Reto	30.01.2015	TRUE	FALSE	FALSE	TRUE	FALSE	FALSE	1	5
Antonio	30.01.2015	TRUE	FALSE	TRUE	FALSE	FALSE	FALSE	4	360
Rüedi	02.02.2015	TRUE	FALSE	FALSE	FALSE	TRUE	FALSE	2	26
Gianni	24.02.2015	TRUE	FALSE	FALSE	FALSE	FALSE	TRUE	1	2
Roland	01.01.2016	TRUE	FALSE	TRUE	FALSE	FALSE	FALSE	2	10
Salvatore	01.01.2016	TRUE	FALSE	FALSE	TRUE	FALSE	FALSE	3	37
Pietro	11.01.2016	TRUE	FALSE	FALSE	FALSE	TRUE	FALSE	1	3
Marco	19.01.2016	TRUE	FALSE	FALSE	FALSE	TRUE	FALSE	1	2
Fabio	06.02.2016	FALSE	TRUE	TRUE	FALSE	FALSE	FALSE	3	#N/A
Philipp	08.02.2016	FALSE	TRUE	FALSE	FALSE	FALSE	TRUE	2	10

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

	2015			2016
	Jan	Feb	…	Jan	Feb
Occ Accident	5	2		4	0
Non Occ Accident	0	0		0	2
Head	1	0		1	1
Torso	1	0		1	0
Upper Extremities	2	1		2	0
Lower Extremities	1	1		0	1
Days off	389	28		52	10

<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

 

[Noodleski]

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.