I have a list with more than 10 000 rows containing Units/Sub-units. Each unit can have from 1 to n sub-units, and are characterised by a set of properties as shown below.
Unit Sub-unit Property1 Property2 Fabrication status
A 1 a d Not started
A 2 b d Started
A 3 a e Completed
B 1 b d Completed
B 2 a e Completed
C 1 b d Started
C 2 b e Completed
I want to use an Excel Pivot table to
a) Report number of unique units (A, B, …)
b) Display a frequency distribution table, i.e. how many units are occurring once, twice etc. in the list. Example:
Occurrences # Units
1 8115
2 1188
3 596
etc.
c) Report the number of Units that are 100% complete, i.e. Units where all Sub-units have fabrication status Completed. Example: In the example above, only Unit B complies with this requirement and should be included in the report.
Unit Sub-unit Property1 Property2 Fabrication status
A 1 a d Not started
A 2 b d Started
A 3 a e Completed
B 1 b d Completed
B 2 a e Completed
C 1 b d Started
C 2 b e Completed
I want to use an Excel Pivot table to
a) Report number of unique units (A, B, …)
b) Display a frequency distribution table, i.e. how many units are occurring once, twice etc. in the list. Example:
Occurrences # Units
1 8115
2 1188
3 596
etc.
c) Report the number of Units that are 100% complete, i.e. Units where all Sub-units have fabrication status Completed. Example: In the example above, only Unit B complies with this requirement and should be included in the report.