Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 4th 17, 08:30 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2017
Posts: 12
Default 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)


  #2   Report Post  
Old October 4th 17, 08:54 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,713
Default 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
  #3   Report Post  
Old October 4th 17, 04:45 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2017
Posts: 12
Default 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
  #4   Report Post  
Old October 4th 17, 04:55 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,713
Default 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
  #5   Report Post  
Old October 4th 17, 06:55 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2017
Posts: 12
Default 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.


  #6   Report Post  
Old October 4th 17, 07:07 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2017
Posts: 12
Default Help please, this is driving me mad!

What I mean is, a dropbox share invitation. I expect they come through as an email?
  #7   Report Post  
Old October 4th 17, 08:21 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,713
Default 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
  #8   Report Post  
Old October 5th 17, 10:15 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2017
Posts: 12
Default 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
  #9   Report Post  
Old October 5th 17, 10:20 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2017
Posts: 12
Default 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.
  #10   Report Post  
Old October 6th 17, 10:46 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2017
Posts: 12
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Driving Me MAD !!!!! John Calder New Users to Excel 1 August 23rd 09 05:06 PM
This is DRIVING ME INSANE! [email protected] Excel Worksheet Functions 2 September 14th 06 08:09 PM
Need Help, this is driving me nuts heitorfjr Excel Discussion (Misc queries) 2 January 15th 06 03:10 PM
Please Help!! DD:HH:SS to seconds driving me mad nikkimc Excel Programming 5 January 14th 06 02:03 PM
Driving me crazy! RobEdgeler[_7_] Excel Programming 0 October 3rd 05 10:19 PM


All times are GMT +1. The time now is 12:05 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017