Comparing postcodes and booking dates to identify clashes

[ScottishJacqui]

Hi!
I have 2 spreadsheets which I have to cross check regularly. The process of checking is quite time consuming, can anyone suggest a quick and efficient procedure?
Both spreadsheets contain details of venue bookings which have to be compared to prevent double bookings.

The 1st spreadsheet has a single venue date with the venue postcode.
The 2nd spreadsheet has the venue date (but given as a date range, start date/end date) with the venue postcode.

Ideally, I want to compare the venue postcodes and booking dates at the same time (I can only do one or the other just now). So that if a booking appears at the same venue on the same date it will flag up.

Any suggestions???

[ARGK]

Hi
See attached sample to ses if this is what you are after.
Good luck
Tony

[ScottishJacqui]

Thanks for your reply, to add another element to the problem, It would be beneficial to know if the dates from the 2nd spreadsheet are 3 weeks or 4 weeks before or after the date from the 1st spreadsheet. For example, in the 1st spreadsheet there is a booking for 10/12/14. In the 2nd spreadsheet there is a booking between 01/12/14 and 07/12/14 which falls into the 3 weeks after category. Hope this makes sense!! Here is an example of my findings after the time consuming approach lol!

[ScottishJacqui]

Sorry, didn't work for me, when I tried entering new data which I know clashes I get the following:

[FDibbins]

ARK, what is the date is June 14?

Jacqui, is the date range in a single cell, or 2 cells?

If you are using 2007 or later, try this...
=IF(COUNTIFS(Sheet1!$A$2:$A$10,Sheet2!A2,Sheet1!$B$2:$B$10,">="&Sheet2!B2,Sheet1!$B$2:$B$10,"<="&Sheet2!C2)=0,"No Match","Duplicate")

For 2003 and earlier, try this...
=IF(SUMPRODUCT((Sheet1!$A$2:$A$10=Sheet2!A2)*(Sheet1!$B$2:$B$10>=Sheet2!B2)*(Sheet1!$B$2:$B$10<=Sheet2!C2))=0,"No Match","Duplicate")

[ScottishJacqui]

Hi, thanks for the prompt reply. Apologies, I'm guessing more infomation would be beneficial to help me!!
I am currently using Microsoft Excel 2010. I have attached a copy of both spreadsheets that I need to cross check. The perfect solution for me which would save oodles of time is to have a formula that looks at the data from both spreadsheets and tells me if a booking date and venue appear in both spreadsheets, hence the 'clash' element.
Another check I need to carry out is identifying if the dates from the 2nd spreadsheet fall 3 weeks or 4 weeks before or after the date from the 1st spreadsheet. So the comparison would involve checking for date/venue for clashes and date/venue for 3 or 4 week date difference.

[ARGK]

Hi
You asked to check for a MATCH which the formula does do and it works. The #N/A indicates no match. However FDibbins appears to have provided you with a solution.
Tony

Sorry, didn't work for me, when I tried entering new data which I know clashes I get the following: