Help please, this is driving me mad!
 

Any advice, suggestions or help with this would be greatly appreciated. My head's spinning!

I've been trying to analyse data about homes' cavity walls. I've tried using a Pivot Table and got close to it, but couldn't see how to do the "date-span" part in a PT. I have tried adding helper columns with COUNTIFS but I've not quite got the right answer with that.

I've copied a few rows of basic data at the foot of this post, but my spreadsheet contains (among other info):-

a Company Number, company name, an address, assessment date, wall type

"Wall type" is a crucial piece of the information and each record contains one of the following:-

Cavity wall, as built, partial insulation (assumed)
Cavity wall, with internal insulation
System built, as built, partial insulation
System built, as built, no insulation
Cavity wall, filled cavity

My analysis needs to do the following:-

1) If a company has performed an Assessment and the result is "Cavity wall, filled cavity" and there are 1 or 2 performed we flag it as a "?" (a question mark); if the result is "Cavity wall, filled cavity" and there are 3 or more we flag it as an "X" (an X)

2) If a company has performed an Assessment and the result is any of the below, we mark it as a "Tick".

Cavity wall, as built, partial insulation (assumed)
Cavity wall, with internal insulation
System built, as built, partial insulation
System built, as built, no insulation

However, we also need to take the next point into consideration . . .

3) If the assessments were carried out within a 2-day span of each other (if there were assessments performed by the same company within 2 days before and 2 days after each date - indicating there have been a lot done in a short space of time), and there are more than four of them, we mark it with an "X"

I've laid out some test data is below. There are thousands of actual records.

Column A - company number
Column B - company name (the company name is rarely populated for all entries)
Column G - EPC Address (nothing special, just an address)
Column T - assessment date
Column Z - wall type
the data continues to column BD - csco_rural

A B G T Z BD
Company Number Company Name EPC Address assessment_date wall_type csco_rural
781618 Flat 5 Foreland Court 29/07/2016 Cavity wall, as built, partial insulation (assumed)
781618 Flat 8 Foreland Court 28/11/2015 Cavity wall, as built, no insulation (assumed)
781618 Flat 17 Foreland Court 04/11/2015 Cavity wall, as built, no insulation (assumed)
781618 Flat 18 Foreland Court 14/11/2014 Cavity wall, filled cavity
781618 Flat 6 Foreland Court 23/07/2013 Cavity wall, filled cavity
781618 Flat 7 Foreland Court 06/04/2013 Cavity wall, as built, no insulation (assumed)
781618 FORELAND Flat 1 Foreland Court 13/11/2012 Cavity wall, as built, no insulation (assumed)
781618 Flat 9 Foreland Court 03/05/2012 Cavity wall, with internal insulation
781618 Flat 19, Foreland Court 16/08/2011 Cavity wall, as built, insulated (assumed)
781618 Flat 10 Foreland Court 15/03/2011 System built, as built, partial insulation (assumed)
781618 Flat 16, Foreland Court 12/08/2009 Cavity wall, as built, no insulation (assumed)

Help please, this is driving me mad!
 

Hi Steve,

Am Wed, 4 Oct 2017 00:30:39 -0700 (PDT) schrieb Steve:

Any advice, suggestions or help with this would be greatly appreciated. My head's spinning!

I've been trying to analyse data about homes' cavity walls. I've tried using a Pivot Table and got close to it, but couldn't see how to do the "date-span" part in a PT. I have tried adding helper columns with COUNTIFS but I've not quite got the right answer with that.

I've copied a few rows of basic data at the foot of this post, but my spreadsheet contains (among other info):-

a Company Number, company name, an address, assessment date, wall type

can you provide a workbook with the important columns and some comments
what the expected result should be?


Regards
Claus B.
--
Windows10
Office 2016

Help please, this is driving me mad!
 

Hi Claus

have mailed a link to you for sharing. Comments are in the "Notes" sheet.

Thanks,

Steve

Help please, this is driving me mad!
 

Hi Steve,

Am Wed, 4 Oct 2017 08:45:58 -0700 (PDT) schrieb Steve:

have mailed a link to you for sharing. Comments are in the "Notes" sheet.

I did not get a mail.
claus_busch(at)t-online.de


Regards
Claus B.
--
Windows10
Office 2016

Help please, this is driving me mad!
 

Hmm . . . sent from dropbox. I've shared it again and will check later, to see if you've received it.

Help please, this is driving me mad!
 

What I mean is, a dropbox share invitation. I expect they come through as an email?

Help please, this is driving me mad!
 

Hi Steve,

Am Wed, 4 Oct 2017 11:07:19 -0700 (PDT) schrieb Steve:

What I mean is, a dropbox share invitation. I expect they come through as an email?

i sent you an email.
It is a little bit tricky.
Try in row 2:
=IF(SUMPRODUCT(--($A$2:$A$50=A2),--(MONTH($T$2:$T$50)=MONTH(T2)),--(DAY($T$2:$T$50)DAY(T2)-5),--(DAY($T$2:$T$50)<DAY(T2)+5))=4,0,1)
or
=IF(COUNTIF($A$2:$A$50,A2)/(MAX(IF($A$2:$A$50=A2,$T$2:$T$50))-MIN(IF($A$2:$A$50=A2,$T$2:$T$50))+1)=4,0,1)
The second formula must be inserted with CTRL+Shift+Enter.

Look which formula gives you the expected result.


Regards
Claus B.
--
Windows10
Office 2016

Help please, this is driving me mad!
 

Thank you Claus, I'll give it a try in a while. I haven't received an email from you. Best email for me is probably pc.tamers(at)outlook.com

Help please, this is driving me mad!
 

On Thursday, October 5, 2017 at 10:15:39 AM UTC+1, Steve wrote:
Thank you Claus, I'll give it a try in a while. I haven't received an email from you. Best email for me is probably pc.tamers(at)outlook.com

Got the email, thank you.

Help please, this is driving me mad!
 

Hi Claus

just emailed you. The formula is sooooo close to correctly flagging the dates but seems to work on day and month, but not year, so if I put in the same date (day and month) but different years, it's showing them as a match.


Regards

Steve