Advice please-Need to track devices, issue and dates

[djhurt]

I haven't used excel for any more than the simplest of tasks for several years. Maybe what I need cannot be done with excel. I need to daily track devices, issues with each device and date the issue occured. I need to have all previous issues/dates with each device to watch for trends/reoccuring issues with the same device. I'm including the spreadsheet I threw together to get me by for now. I need something a bit more dynamic, and it seems I need another dimension for data but I can't wrap my head around a way to do it in excel.

Looking at my example attached, this simple method works fine, and I can simply sort by date. But whenl I need to add another date/issue/resolution to a device number in column A, how can I keep all the data together with it's device number? And how can I make it easy to add more dates/issue/resolutions to each number every day? Appreciate any advice anyone can offer.

[DaveDeV]

Your lack of use of Excel for several years is likely to be a minor obstacle as, over this period, Excel has come quite way...

Ideally, what you need is a few tables...

Table 1: Device Register - lookup table (Device Ref#, Device Description, Dept Ref#, Department Name)
Table 2: Status Table - validation list (Logged, In Progress, Resolved, Scrapped)
Table 3: Department Table - lookup table (Dept Ref#, Department Name, Contact Name, Contact Tel No)
Table 4: Issue Register - data table (Issue Ref#, Device Ref#, Device Description, Dept Ref#, Department Name, Date/Time Logged, Status, Date/Time Closed

Tables 1-3 would be used to assist in maintaining Table 4.

Table 4 would be the basis for Pivot Table generation by the following:

a) List of all unresolved issues by department
b) List of all unresolved issues by device
c) List of all issues over time by device (you could include effort and/or cost in Issue Register to augment your info)
etc..

You need to advise of the number of devices under your beady eye...

Dave

[djhurt]

Thank you for your help. I have over 200 devices I am tracking. I'm working on your suggestions and trying to piece together everything in way I think you intended.

[DaveDeV]

Happy hunting!

Dave

[djhurt]

I'm somewhat confused on what your suggesting for table 2. Could you give a bit more detail on what you had in mind there?

[DaveDeV]

Hi DJ,

Table 2 is just a validation list (named "Status_List") consisting of 1 x column of 4 x rows each containing a word describing the status of an issue.
These 4 x status values could be:

1) "Logged" (a fault report has been logged with the support group - i.e. You)
2) "In Progress" (the reported fault is in the process of being addressed by the support group)
3) "Resolved" (the reported fault has been fixed and the device returned to the user)
4) "Scrapped" (the reported fault was not repairable and the device should be scrapped and a replacement procured)

(You may want to add/change/delete options from this list).

In the Issue Register, when entering data, you should have a column headed "Status" and for the cells in that column you need to activate Data Validation using the options of "List" and, for the target range "=Status_List".
This will result in a drop down box being made available for data entry in that column and, when active, the list will contain the word options from the validation list (i.e. Logged, In Progress, Resolved, Scrapped).

Apologies if I over-simplified - I've just woken up...

[djhurt]

Alright, I have my device table which is a column listing each device by it's unique number. I have a "Status_List" column with my status items. I have a "Status" "List" giving a drop down menu from the Status_List. Can you elaborate on step 4 mentioned above? Also can you give me an example how tie all of this together?

Thankyou

[DaveDeV]

Hi DJ,

Its 23h25 here and I'm for bed.

Will take a look tomorrow

G'Night!

Dave

[djhurt]

I've done some more work on this. I've changed things a bit based on your suggestions, to fit what I need to track. I've got a table with each devices unique number. Next column is where I'll input a brief descripton of the issue. Each issue will be different so these cells will ideally not follow any specific text like the Status_list you suggested. Next column will be the date of the issue. And the last column will be the Status column you suggested, using data validation from "status_list". I think what I'm not able to grasp, is how do I link the data to each device number, and maintain a chronological "growing" list of issues for each device number?

[djhurt]

Ok, nevermind my last question, I think I've got some of this figured out. I've got 1 table consisting of a column with device by unique number. Next column I can enter the issue/problem. Next column is the date and the last column is the status, coming from the "status_list" you suggested above. I've now got a pretty slick table to enter all this data into, but now I'm at the question of how do I add more dates/issues to each device number and keep it all together? What I need to do is provide a weekly report to the boss so we can spot any trends(reoccuring issues) over a given week, month, year etc. Thank you so much for your help so far!

[DaveDeV]

Hi DJ,

Apologies - have been working my *** off all day and its again 22h00-ish and I'm knackered...

In your main "Issue" table, you will have fields that include:

a) Date Raised
b) Device Number (Unique)

In your "Device" table, you will also have the Device Number.

What you need to do when setting up the "Device" table is to name two arrays:

a) "Device_List" which needs to be a single column array including all of the entries in the table for the Device Number field
b) "Device_Table" which needs to be a multi-column array including all rows and all columns

Insert a row above the column headings and enter numbers from 1 to "n" for the columns in the Device_Table (these are your column offset values when using VLOOKUP and save you having to count columns every time.

In your "Issues" table in the Device Number field you can now add Data Validation to the Device_Number column using the "List" option and in the Values field, type:

=Device_List

What this does is to use the first column of the Device_Table containing all the valid Device_Number values to create your drop-down selection list.

Selecting one will populate the active cell with the selected value from the Device_List, ensuring that the value in that cell is a valid Device Number in the device table.

To retrieve any other fields from the Device Table - such as Device_Name, etc - your formula would now be:

=IFERROR(VLOOKUP(Device_Number,Device_Table,nn,FALSE),"Error")

where:

Device_Number is the cell reference where the selected Device_Number was populated
Device_Table is the entire array
nn = the offset (column number) of the field you want to retrieve
FALSE ensures that you use a valid number and returns an error message on any mismatch (which shouldn't happen given that the Device_Number was valid to start with.

Use the same logic for Status_Table/List and Department_Table/List and Employee_Table/List

In this way you "link" the tables via VLOOKUP using the common unique "key" field.

This is similar in concept to the way a relational database is set up but without the automatic linking and with less efficient data rationalization.
Despite the lacks of linking and lower efficiency, it works quite well and allows one to maintain separate smaller tables when any "dataset" changes.

I hope that helps as I'm off to shower and head for bed.

G'Night mate

Dave

[DaveDeV]

Wow! That was one bad case of a stutter...

[DaveDeV]

Hi DJ,

Today has been a bit more sane....

I've knocked together a sample workbook for you containing:

a) Reference tables (Department, Status, Device)
b) Issue Register table
c) Two sample Pivot Tables (all items and summary for a month)
d) One sample Pivot Chart (summary for a month as a bar chart)

This should give you an idea of what's possible...

Happy hunting.

Dave

Attachment:

DeviceServiceRegister-Sample-DdeV.xlsx

[djhurt]

Ahh! That's awesome you did that. I was just replying with some confusion when I saw you had replied again

[djhurt]

Nevermind

[DaveDeV]

Hi DJ,

23h20 and I'm starting to flag...

Is it necessary in this table to hold a detailed description of the problem?
Or, would it be acceptable to come up with categories of problem and just use those?

Personally, I would separate detailed fault information from incidence data - the two together make for cumbersome structures in the workbook as well as cumbersome data entry. Each incident should have a paper "job card" associated with it (additional field if necessary to link incidence to fault info on job card - Job Card No). The separate job card provides with two sides of paper real estate to allow you go totally moggy with your record-keeping...

If categories of issue are acceptable, add another combo table (list/table) and decide whether or not you want to used codes + category or just category text. Obviously, you will need to add one or two columns to the Issue Register (depending on need for code+text or text-only)

If you do go with categories, keep them simple and not too numerous.

BTW, you could also add warranty info such as the period of the warranty and calculate warranty expiry date by adding period to purchase date and then adding one more calculated field - Warranty_Status ("In" or "Out") to allow pivot reporting by Warranties due to expire in next month or some such...

Anyway, eyelids are obstructing my view of the screen...

G'Night

Dave

[DaveDeV]

Hi DJ,

BTW, you can use Excel to create a separate Job Card template with all the fields you could need (hours_effort, charge_rates, cost of spares, etc)
When completed, use Save As to save a unique filename such as:

JobCard-9999-YYYYMMDD.xlsx

where:

9999 = Device No as per incidence log
YYYYMMDD = date opened

Then you could, if you want to, link the Job_Card file to the record for that incident by pasting the filename into a new field and converting the pasted value into a Hyperlink.

This would allow you to view the incidence log and, when needed, open the detailed job card file.

Just a thought...

Oh yes.. And you could also consider adding a unique incident key value to the incident table to allow direct retrieval for a "single record enquiry" when the table gets too large to allow easy sequential visual search. Otherwise, just use the filtering capabilities to narrow down the options in stages

Dave

[djhurt]

Ok, I think were definately on the right track here. I've got a pivot table displaying the data I want now. Only problem for me now is how to arrange the way the data/fields are displayed. I've tried several different arrangements in pivot table field list but them behind that doesn't make much sense. I looked at your example and tried to arrange things the same with different results. I have a Device, issue, date and status field. What I'd like is how your example was displayed. Device numbers down the left column with date, issue and status across to the right for each device. Any suggestions on this?

[DaveDeV]

Hi DJ,

Excel's pivot table menu group includes a button for selecting report layout.

To get there, you need to click on a part of the table to activate pivot table tools (a pink "supertab" on top menu bar).
Then click "Design" sub-tab and the Report Layout drop-down button is activated. Select the drop-down option by clicking on the inverted triangle.
The third option presented is "Show in Tabular Form" - select it.

The indented single column you've probably been getting to-date will be replaced by multiple columns (one per indent level).

Once you're in Tabular Form report mode, you can add descriptor fields to the row heading section. The only nuisance aspect is that each is activated with Sub-Total set to "On" - to de-activate, right-click on the column heading and de-select the sub-total option. This will result in the added descriptor fields all appearing on the same report line with no sub-totals.

To de-activate row or column totals, use the same approach by right-clicking on the total line - although the prompt now says "Remove Totals".

EDIT: Oh yes, and if you right-click on a column heading in a pivot table, you can also move the column to the left or right by selecting move left or move right - this will allow you get the sequence the way you want it.

And that should sort you out mate!



Best Regards,

Dave

PS: The link below will take you to a post that shows a 12/13 month rolling extract pivot table that takes the current date and calculates the end of the previous month and the start date of the rolling period and then just displays a summary of items between those dates. There are also a couple of niceties such as a variable highlight threshold so that someone viewing the table can select a threshold and only highlight values >= the threshold. There's also a row of sparklines at the bottom to depict trends.

http://www.excelforum.com/excel-tips...html?p=3244392

[djhurt]

I"m still working on this, I haven't abandoned! I was off-site this last week so I wasn't at my desk. I will reply with my results when finsished but wanted give thanks again for all the help you've provided so far.

[djhurt]

Ok I think I've this doing what I need it to do and it's looking pretty good! My only problem is I can't seem to get the pivot table to sort by date. I have a date column in my source table, I've designated to display my specified date format, but sorting the pivot table by the date column gives unexpected results. Nothing like oldest to newest and vice versa. Any suggestions?

[djhurt]

I think I've got everything the way I had hoped for. A huge thanks to your help on this. Your suggestions really got me goin on this and I'm confident this will grow/shape as time goes to fit any changes in the future. Thanks again DaveDev!