Help with formula or pivot

[Laurentl]

Hi all,

I have a challenge with some spreadsheet data that I am trying to analyse

In my team we receive a daily report related to some IT kit and whether it is responding or not. The report is quite basic and so every day this report will tell us the IT hardware ID, the date it was installed and the date it was last communicating. Therefore when I layer them up into a single spreadsheet there same hardware ID may appear multiple times.

We are looking to understand what kit is permanently down and what is behaving intermittently

The report we receive is in the format below (sample data below layered over 2 days - note the same ID repeated twice in second column as example):

Report Date Hardware ID Install Date Last Successful Ping Days Unresponsive
19/07/2015 123 1/01/2015 10/07/2015 9

20/07/2015 123 1/01/2015 10/07/2015 10
20/07/2015 456 2/02/2015 05/07/2015 15

The summary we are trying to produce looks like this:

Hardware ID (unique list down) Dates (each day populated to the right) with X or something to show if it has not communicated for that day
123
456


As the data is 'layered' I am struggling to think of a way to analyse and interpret it to get to the output I would like to show. Please could someone help with some guidance to produce this using formulae or pivot tables?

Many thanks!

[JeteMc]

See if you are looking for a solution like this hybrid Pivot/Formula Table (attached). If so I'll get you the particulars for creating one for your data, it would be helpful if you could "Go Advanced" and upload a desensitized copy of the data.

IT kit report.xlsx

Hope this helps.

[Laurentl]

Thanks - Resolved

Not ideal but I ended up with this solution:

Extra calculated columns in my source table
Pivot table with some calculated fields
Secondary table driven off the pivot table as I found there are identifiable data gaps in my source data I needed to fill in via formulae
Calculations off the secondary table

Not pretty - but it works for now!

[JeteMc]

Glad that you got an answer. Please mark that thread as solved, and if anyone was helpful please add to their reputation. Have a good day.