How to compare 2 date ranges when name matches
Requirement:
The user wants to match the Names in the sheet with other sheet and than match the associated date ranges against those names and return me the value of "match" or "no match"
The are 3 sheets: vacation calendar and on call sheets.
The user would to match name(Column A) in vacation calendar with the names as primary and secondary column in Oncall Emea sheet (Column C & D), and the date rages in both and if date ranges collides and name also matches then it should return a value of "match" if true and "non-match" if false.
Solution:
=IFERROR(IF(D2="OnCall EMEA",ISNUMBER(MATCH(1,INDEX((('Oncall Emea'!C:C=A2)+('Oncall Emea'!D:D=A2))*(('Oncall Emea'!A:A>=B2)*('Oncall Emea'!A:A<=C2)+('Oncall Emea'!A:A<=B2)*('Oncall Emea'!B:B>=B2)),0),0)),IF(D2="OnCall AMER",ISNUMBER(MATCH(1,INDEX((('Oncall AMER'!C:C=A2)+('Oncall AMER'!D:D=A2))*(('Oncall AMER'!A:A>=B2)*('Oncall AMER'!A:A<=C2)+('Oncall AMER'!A:A<=B2)*('Oncall AMER'!B:B>=B2)),0),0)))),"NA")
Obtained from the OzGrid Help Forum.
Solution provided by NBVC.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to consolidate data into single sheet from the selected spreadsheets |
| How to get Monday's date when you enter any other date for the week |
| How to use a single 'date field' for various items in a data entry userform |
| How to delete row if Date/Time between 05:00 - 20:00 |
| How to create a macro button to put date in selected cell within specific column |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
