Confused about what method to use for extracting data

matthewoaks

New Member
Joined
Jan 31, 2014
Messages
30
Hi,
I am brand new to the forum so please bear with me if I'm a little long winded
I have been looking around on the internet and Youtube to try and figure the best approach I should take for extracting and displaying data. The list is based on observations that come in from auditors and these are recorded to identify trends and repeated observations to try and avoid them in the future.
I'm not sure if this is the best method but I have put the list in a table so that it can be filtered but don't know how to go about summarising the data in either graphical form or a smaller table alongside.
In the table there are 5 main columns; Vessel Name Date of Inspection SIRE VIQ Chapter Question Ref Inspectors Comments
Now each vessel has two inspections per year (6 vessels).
For each inspection a vessel might pick up a number of observations. These obs are split into chapters for the relevant part of the ship ie. 1.General Info, 2.Certification, 3.Crew Management, 4.Navigation and so on (there are 13 chapters in total).
In each chapter there are a number of questions that the auditor surveys the vessel against i.e Chapter 1, Q1.1, 1.2, 1.3, 1.4 and so on for each chapter.

I have been asked to try and display the data so that we can easily spot which vessels are getting repeated observations for a particular chapter and more importantly for a particular question.
If you need me to attach the Excel Workbook then it isn't a problem but I need help on how to do this

Look forward to hearing your suggestions
BRgds
Matt
 

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,116
Office Version
365
Platform
Windows
Hi Matt,
to attach the workbook, you'd need to put it on a Dropbox/Skydrive/something like that and post the link here. I'd happily have a look at it. How are your Excel/VBA skills?
Cheers,
Koen
 

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,116
Office Version
365
Platform
Windows
Hi Matt,

https://www.dropbox.com/sh/xkm0icjqiuo9yvd/EhV35QF8Eg

What I did: "enriched" your data, calculating for each ship the last checked date, the date before and the date before that. Gave them a label (column G). Then calculated whether the error was recurring (column K and L) for the last 2 checks. With that data it's lots easier to create tables.
On SHIPOVERVIEW I made a pivot table with some conditional formatting, basically showing the errors in yellow, repeating once orange, repeating twice red. The pivot coloring only works for 1 ship. Next, on the TRENDS sheet, I created two small tables (with a formatting I developped and got to like as I find it clean and friendly), one for the number of errors, showing a development in that, and one for the repeating issues. In both cases, I used conditional formatting to highlight the noteworthy numbers.

Hope these examples help,

Cheers,

Koen
 

matthewoaks

New Member
Joined
Jan 31, 2014
Messages
30
Good morning Koen,
First off thanks again for looking at the spreadsheet for me, there's some Excel wizardry that's beyond my skills :). There's just a couple of Q's if I may. When I enter more lines in the observations table, how do I get the formulas to automatically take into account the new rows of data.
Also we want to trend all audits, from what I can assume, your formulas pick up the last 3, is that correct? Say we have 5 years worth for one ship, that's 10 audits, would the revised workbook take them all into account?
BRgds
Matt
 

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,116
Office Version
365
Platform
Windows
Hi Matt,

okay, then the next version might be even more wizard-like ;). So the main thing I did to solve your problem of growing data tables is defining the range as a table (in the menu: Insert-> Table), I named it TableOperations. That causes the range to be filtered, the striped colors (that you can change, just click anywhere in the table and then use the extra menu that pops up in the ribbon) and if you add a row, they will automatically be added to your formula. The formulas have therefore changed too:
From something like
=COUNTIFS(OBSERVATIONS!$B$7:$B$175;D$81;OBSERVATIONS!$D$7:$D$175;$B82)
to
=COUNTIFS(TableObservations[Vessel Name];$B20;TableObservations[InspNr];1)
They are more readable. Your pivots are also linked to that table (a special kind of named range), so no need to select whole columns or big ranges, the data will be OK if you refresh.

Secondly, I changed the columns I made a bit. What I wanted to achieve was two things:
-Have a column where your last audit for a specific ship had a 1, the audit before a 2, etc. That was a bit complicated, but with some help from this site (Count unique distinct values that meet multiple criteria in excel | Get Digital Help - Microsoft Excel resource) I got the formula working and entered it in column G. It is an array formula that basically calculates the amount of unique values for that ship where the date is >= the date in that row.
-The next column is using that number to check whether that same error was found in the check before.

With that information it's pretty easy to make pivots. I updated the one on SHIPOVERVIEW, now all ships fit in one pivot. And in this way you can have as many audits as you like.

The file is here:
https://www.dropbox.com/sh/l7ywfwzfk5j20sr/dJ6Jk0NGyM

G'luck with the next steps :),

Koen
 

matthewoaks

New Member
Joined
Jan 31, 2014
Messages
30
Hi Koen,
That looks cool to me, seems to be working OK. I've tried to do it myself but can't work out how it's done, I'm assuming one more column will need to be added to the table.
Instead of having the '1' cells highlight different colours in the pivot table on 'SHIPOVERVIEW' worksheet dependent on how many 1's occur, it would be easier for us if there was a total column say next to the Question ref that totalled the number of 1's in the row and then conditional formatting added to that column with maybe a colour scale i.e 1=green through to >4=Red
Would that be possible?
BRgds
Matt
p.s. if you send me your PayPal details I'll whip you across a little contribution for all your hard work so you can get yourself some beers. I wouldn't have got anywhere close to what I have at the minute if it wasn't for your help.
 

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,116
Office Version
365
Platform
Windows
Hey Matt,

for conditional formatting of Pivot tables:
Conditional formatting for PivotTables in Excel 2010 and 2007
Unfortunately it's not possible to only give the totals a conditional format when there is data in various columns/rows. So then you'd probably want to remove the InspNr from the Columns, see the updated file I created. The only consideration: if you have one ship with 5 audits and a ship with 10 audits, it's logical that the latter will have more "red" numbers, as there have been more audits... If this were my business I'd probably want an average number of "errors per audit", have an average of that and the difference from that average. I made a small table on TRENDS that highlights the audits with >1,5 times the number of errors of an average of that "audit round".
On tables, e.g. check out this site:
Working with Tables in Excel 2013, 2010 and 2007
Or just try the mini tutorial I made in your file :)
https://www.dropbox.com/sh/l7ywfwzfk5j20sr/dJ6Jk0NGyM

I'm not using paypal, do have a bitcoin wallet somewhere, but I'm not sure whether you're into that. But a like is also worth a lot ;).

Cheers,

Koen
 

matthewoaks

New Member
Joined
Jan 31, 2014
Messages
30
Hi Koen,
Sharing your knowledge and generously giving your time up to spend on this workbook was more than I expected. Thank you so much for your help.
Everything looks absolutely perfect now after those couple of additions you made yesterday.

Thanks Again
Matt
 

matthewoaks

New Member
Joined
Jan 31, 2014
Messages
30
Hi again Koen,
I know I said earlier that everything was perfect. Well I just came across one more thing that you might or might not be able to help with.
I've just entered another inspection into the obs table for the Jaynee W, there was an instance when there was 2 obs in chapter 2 and 4 obs in chapter 8. When I looked at the trends tab under the number of repeating observations per audit, it picked up the 2 repeating instead of the 4.
It would be my pref for it to pick up on the higher number because if we get a certain number of obs in one single chapter, the ship could potentially get put on technical hold! Is there any way to do this?
I've uploaded the file I've been working on. (I made a few aesthetic changes to fit in with our company form colours)
https://dl.dropboxusercontent.com/u/86401484/SIRE%20Observation%20Trending%20v3.xlsm
If it cannot be done that's absolutely fine, it does a cracking job as it currently stands
BRgds
Matt
 

Forum statistics

Threads
1,078,243
Messages
5,339,048
Members
399,276
Latest member
BlueCheetah

Some videos you may like

This Week's Hot Topics

Top