Feb 18 2018
08:13 AM
- last edited on
Jul 25 2018
11:05 AM
by
TechCommunityAP
Feb 18 2018
08:13 AM
- last edited on
Jul 25 2018
11:05 AM
by
TechCommunityAP
Hello,
Allow me to explain what I am trying to achieve. I have 2 worksheets of data with a common field of an ID #. In sheet 1, the ID # is in cells F2:F88. In sheet 2, the ID # is in cells F2:F48. I also have a start date and end date on each sheet. In sheet 1, the start date is in cells G2:G88 and the end date is in cells H2:H88. In sheet 2, the start date is in cells G2:G48 and the end date is in cells H2:H48. I am trying to write a formula that would tell me if the ID from sheet 1 matches the ID # from sheet, then perform the function of if the start date from sheet 2 is greater than or equal to the start date from sheet 1 and the end date is less than or equal to the end date from sheet 1, return Y, otherwise return N. I am unsure of how to write this. Essentially, I am trying to compare the see if the date range from sheet 2 is within the date range on sheet 1, if the ID # is an exact match. My end goal is to produce a list of ID's from sheet 1 that do not have a data match on sheet 2.
Thank you.
Feb 18 2018 08:54 AM - edited Feb 18 2018 08:56 AM
Hi Lori,
If your end goal is to produce a list of ID's from Sheet1 that do not have a data match on Sheet2, then you can depend only on the ID in the matching process, you don't have to check the start date and the end date of each record.
So you can use this formula in cell I2 of Sheet1 to find the records that aren't in Sheet2:
=IF(ISNUMBER(MATCH(F2,Sheet2!$F$2:$F$48,0)),"Y","N")
Please find the attached file.
Feb 18 2018 09:15 AM
Feb 18 2018 09:41 AM
Hi,
No matter if there are multiple hotel reservations per ID, that formula will tell you that at least there is one hotel reservation for the ID or not!
It matches the first occurrence of the ID and ignores the rest!
If there are no hotel reservations for the ID, it will return the value of "N".
The good news that the hotel reservations that fall outside of the date range don’t matter to you, so you can depend on that formula that will take into account only the ID!
Feb 18 2018 09:54 AM