Analysing Data in Excel

[KB2015]

I am really struggling to analyze the attached spread sheet to extract the data I want.

What I would like is a total of all numbers used (1,2,3,4,5,6,7,8,9) to analyze so I can say for example in total we had x amount vehicles inspected under 1 conditions.

I am looking for a formula or pivot table to total the number of 1's,2's, 3's, etc overall over the 2 year period.

Many thanks if anyone has any suggestions!

[Richard Buttrey]

Before we go too far with the existing layout are you willing to change it? Currently it's not laid out in the best way for analysis.

For any serious anlalysis, whether with a Pivot Table or Functions, you should always create a normalised two dimensional table. In your case you should have columns for

Date (either actual date or perhaps week ending or week commencing date)
Equipment Type (i.e. Vehicle or Trailer
Vehicle Category (i.e. Mainland, Mainland 1, Island...etc)
Vehicle Number
Value

You have 340 Vehicle records and 1150 Trailer records
Each record would be one row so the table would be 1150 + 340 rows by 5 columns.

[KB2015]

Hi Richard, many thanks for your response. With your fresh outlook on it I can see organizing it in your categories would be much better. I will do this and I think I can do pivot from that.

[Richard Buttrey]

Hi,

Let me know if you need any additional help.

If the existing layout comes from some other system and you can easily change that to the 5 column layout you require then great. Otherwise I could write a macro to convert your existing data to the 5 column layout if you struggle.

[KB2015]

Many thanks I will do

[Richard Buttrey]

It only took a few minutes so I've created the single database in the attached and added the Pivot Table
I realise the data may have changed so you may need to use the macro ListData to recreate a new database.

The macro is currently pointing to sheet2 the Trailers sheet, so after any rerun you will need to edit and change any reference to Sheet2 to Sheet1 to run the Vehicles data. The macro creates a table starting in B2 on the Database sheet each time so you'll need to take a copy of the first run of data so that you can copy it back underneath the second data run.