Logic Question - Complicated Criteria

[Ark68]

I have a database that maintains information of a customers rental of a facility.
Normally, the customer rents "room A" for a large block of time, say 5:30 PM - 8:30PM, for which their is one simple record for this rental.
On occassion though, the customer will rent that same block of time between two or more rentals, say 5:30-6:30PM and 6:30PM - 8:30PM. Two separate records share the same information (contract number, location and activity) with the exception of the start/end times.

With the help of individuals here at Mr. Excel, using a complex combination of named ranges, advanced filters, and pivottables I have managed to find and merge the duplicate pairs so that the two records become one with the appropriate start and end times.

But I've ran into a scenario where this is inappropriate. There are instances where there are two records, both with the same contract number, location and activity, and both with different times. Normally, the code would merge the two with the lowest of the two times being the start time, and the highest of the two being the end time. This is quite appropriate when the two rentals are back to back. However, this would be inappropriate if, although information was the same, they were not back to back. So, two identical records, with one being from 5:30 - 8:30 and a second from 9:00 - 10:30 must not be merged, but be treated at unique.

So my database (sorted in descending order by facility) has three flagged columns (NOP)for use in filtering.

Column N is a concatenation of contract number, facility and activity.
Column O {=COUNTIF(N:N,$N8)>1} returns true if an identical value is found in column N, false if it is unique. All those returning true will have one or more identical records according to the value in column N
Column P {=IF($N8=$N7,"DUP","")} identifies the duplicate(s) within any group of matching records.

Before any filtering is done, a pivvotable is refreshed in which the the count of identical records (column N) is calculated, in addition to the min start time of each group of identical records. This pivottable forms the seach range for the vlookup formula which changes the start time of the merged records.

Advanced filters are applied to filter all "True" values in O, and "DUP" in P.

Those records filtered "DUP" will be deleted from the database, leaving only one unique record from each group. That rental number is crossreferenced withthe pivottable and the min value for it's corresponding start time is substituted into the appropriate cell in the database.

My question ... the use of the formula in column P, used to establish which rows get deleted, isn't accurate. I need to somehow assign "DUP" only to those identical records (column N) in which the start time of the second record is 30 minutes or more than the end time of the first? Those that have a gap of 30 minutes or more will be unique, not labelled "DUP" and thus will not be deleted.

See sample below ...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
3	1	50816	3:00PM	5:00PM	50816NorthfieldParkField	TRUE	DUP		Records1and2,althoughidentical(E)are30minutesapart,
4	2	50816	5:30PM	8:30PM	50816NorthfieldParkField	TRUE			unlikebeingbacktobackliketheothers.
5	3	90492	5:00PM	6:30PM	90492RIMParkOutdoorFacilitiesBallDiamond#1	TRUE	DUP		Deleted
6	4	90492	6:30PM	8:00PM	90492RIMParkOutdoorFacilitiesBallDiamond#1	TRUE	DUP		Deleted
7	5	90492	8:00PM	9:30PM	90492RIMParkOutdoorFacilitiesBallDiamond#1	TRUE	DUP		Deleted
8	6	90492	9:30PM	11:00PM	90492RIMParkOutdoorFacilitiesBallDiamond#1	TRUE			90492	5:00PM	11:00PM	90492RIMParkOutdoorFacilitiesBallDiamond#2
9	7	90492	5:00PM	6:30PM	90492RIMParkOutdoorFacilitiesBallDiamond#2	TRUE	DUP		Deleted
10	8	90492	6:30PM	8:00PM	90492RIMParkOutdoorFacilitiesBallDiamond#2	TRUE	DUP		Deleted
11	9	90492	8:00PM	9:30PM	90492RIMParkOutdoorFacilitiesBallDiamond#2	TRUE	DUP		Deleted
12	10	90492	9:30PM	11:00PM	90492RIMParkOutdoorFacilitiesBallDiamond#2	TRUE			90492	5:00PM	11:00PM	90492RIMParkOutdoorFacilitiesBallDiamond#2
13	11	90492	5:00PM	6:30PM	90492RIMParkOutdoorFacilitiesBallDiamond#4	TRUE	DUP		Deleted
14	12	90492	6:30PM	8:00PM	90492RIMParkOutdoorFacilitiesBallDiamond#4	TRUE			90492	6:30PM	8:00PM	90492RIMParkOutdoorFacilitiesBallDiamond#4
15	17	91028	6:30PM	7:15PM	91028RIMParkOutdoorFacilitiesBeachVolleyballCourt1	TRUE	DUP		Deleted
16	18	91028	7:15PM	8:00PM	91028RIMParkOutdoorFacilitiesBeachVolleyballCourt1	TRUE			91028	6:30PM	8:00PM	91028RIMParkOutdoorFacilitiesBeachVolleyballCourt1
Sheet1

Jenn

One filter is applied that will isolate all records having more than one matching (rental number+facility+activity) concatenation. If there is more than onee matching instance, this column is "TRUE", and the filter will filter all records not true.

A third column looks at the f

 

[LxQ]

So, if I see it correctly, you have the starting and ending times in L and M.

Column P {=IF(AND($N8=$N7,$L8-$M7<0.020834),"DUP","")}

The 0.020834 is 30 min.

 

[Ark68]

Thank you LxQ,

I'll have to wait until tomorrow to try it out and let you know the result. The times for comparison reasons are actually in columns C and D. I'll make the changes to the formula!

Jenn

 

[Ark68]

Hi everyone,

LxQ, I've adapted your suggested formula ...

=IF(AND($N8=$N7,$C8-$D7<=0.020833),"DUP","")

.
and it works great!!! Thank you LxQ!