TechNote: MS Excel Drawing Register P-51


TechNote: MS Excel Pivot Tables: Drawing Register P-51

The drawing archive collection for the Mustang P-51 includes an NAA Document register in PDF that lists all the Scan Index Numbers, Drawings Number, Aircraft Type and Change (Revision)Number.

To make sense of this large archive; containing thousands of scanned images; it is necessary to first transpose the comprehensive NAA document register into a spreadsheet in order to analyse and filter the data according to requirements.

My requirements are simply to be able to group the data per content; Fuselage, Wings, Equipment etc; and per aircraft type; P-51A, P-51B, P-51C etc.

Further breakdown of data would involve isolating the main assemblies and then parts or sub assemblies belonging to each.

2015-06-12_22-06-44From Adobe Acrobat I extracted the pages of data as spreadsheet tables to which I added a Drawing Description and grouped the data sets together by “Content”…that took a long time to do as the extracted data first had to be checked and then sorted accordingly.

The drawing descriptions came from an index already created by Norman Meyers at Chanute Air Museum, so it was relatively easy to enter this data into my spreadsheet. Its a real pity I had not had access to Normans data earlier; could have saved me a lot of work.  My thanks to Norman Meyers.

2015-06-12_23-39-41After sorting the data and inserting descriptions I now have separate worksheets for the content similar to this one.

What I really want now is to identify and organize the drawings belonging to each type of aircraft. For this exercise I use the Pivot Table function in Excel. Pivot Tables are great for organizing and summarizing data according to specific criteria.

2015-06-12_22-34-13Here I have initiated the Pivot Table function and selected the entire data-set of information relating to the Fuselage; as you can see we have a large number of drawings just for this one area!

When working with large data-sets it is good practice to select a new worksheet for inserting this new table.

2015-06-12_22-38-07What we end up with is a new worksheet with the pivot table outline on the left and a selection box on the right. We now select from the latter the columns of data we want…in this case all the main ones plus the P-51D; which will populate the outline table on the left.

Pivot tables by default include a summary row under each entry; I suspect this is more useful for statistics than organizing a document register; which we don’t want.

2015-06-12_22-49-34To remove the summary from the table we just need to select each column in turn using the small arrow as highlighted and turning off this option in “Field Settings” and select “None”.

The final step is to filter the data according to the required criteria; in this case I want all the drawings that have an “X” value in the P-51D column.

2015-06-12_22-54-42This is done by selecting this value from the header drop-down options; which lists by default the unique values in each column from the master table.

We now have a list of all the fuselage drawings and their location in the archive belonging to the P-51D aircraft.

The next step would be to extrapolate all the “assembly” drawings and from there the components that make up each assembly…but that’s for another day.

Pivot Tables are great for this type of job.

  • 846 fuselage drawings for the P-51A
  • 890 fuselage drawings for the P-51B
  • 833 fuselage drawings for the P-51C
  • 923 fuselage drawings for the P-51D
  • 950 fuselage drawings for the P-51H

Many drawings of course are a shared resource for all variants.

For further information on any of these projects please feel free to drop me a line via my contact page or email me at hught2008@gmail.com

Advertisements

About Hugh

BIM & Cad Manager/Strategist, 3D/4D, multi-discipline cad workflows, integration. Interests include photography and historical aviation.
This entry was posted in Aviation, Design, Excel, Pivot Tables and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s