advance filter with or and and fuction

[zakizelani]

hi,

I will like to do an advanced filter so that I can spot duplicate.First, let me go through my sheet first.

I creating a shet for the user to make a booking for their the slot.


1. First user input the start time of the appointment at V3


2. the total hours of the booking at V5 (0.5,1,1.5,2,2.5 Every 30 mins)

3. next they need to book the slot at AY3:BF7


AY:BB BC:BF
[TABLE="width: 500"]
<tbody>[TR]
[TD]Slot 1[/TD]
[TD]1130SL1[/TD]
[/TR]
[TR]
[TD]Slot 2[/TD]
[TD]1200SL2[/TD]
[/TR]
[TR]
[TD]Slot 3 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Slot 4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Slot 5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


for example, the user wants to book at 1130 for 1hr

slot 1 will populate the data validation list of (1130sl1,1130sl2,1130sl3.....1130sl5)

while slot 2 will be 1200sl1,.....1200sl5)


when they click the add booking button, my code will go through and filter to see if there is any duplicate.



[TABLE="width: 601"]
<colgroup><col span="5"><col><col></colgroup><tbody>[TR]
[TD]Criteria[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Slot 1[/TD]
[TD]Slot 2[/TD]
[TD]Slot 3[/TD]
[TD]Slot 4[/TD]
[TD]Slot 5[/TD]
[TD]Appt Date[/TD]
[TD]Store[/TD]
[/TR]
[TR]
[TD] =1130SL1 [/TD]
[TD] = [/TD]
[TD] = [/TD]
[TD] = [/TD]
[TD] = [/TD]
[TD]>=43131[/TD]
[TD]=Kembangan[/TD]
[/TR]
[TR]
[TD] = [/TD]
[TD] = [/TD]
[TD] = [/TD]
[TD] = [/TD]
[TD] = [/TD]
[TD]<=43131[/TD]
[TD]=Kembangan[/TD]
[/TR]
</tbody>[/TABLE]



Range("B8:Z15").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"BI6:BP8"), CopyToRange:=Range("BQ8:BX8"), Unique:=False


However the outcome is not what i want because if the user book at 1130 in slot 2 it will never filter it out as the creteria is under slot 1. what i need is to prevent user to book the same slot on the timing in the same day.



user A : book 1130sl1 in slot 1 on 1/1/21

user B : book 1130sl1 in slot 2 on 1 /1/21

i need them to capture that this is duplicate


user A : book 1130sl1 in slot 1 on 1/1/21

user B : book 1130sl1 in slot 2 on 2 /1/21

this is not duplicate

hope you understand what i want to say here