Hello team,
I am have this sample data where i have sites details from 3 different sources and i need to combine all of this dat in 1 sheet for future work. I believe we would have to do this based on Address however again address is saved in different manner. Could someone help me in this where we have any script to do this for me. Again this is sample data, there is another big data of which i have get this job done
Also i am running this code only on 4 lines for testing however this would have to run on entire sheet.
Hello All,
I am working on a file via which i wanted to check if someone is going on leave then i would have to compare his OnCall schedule and check if the OnCall and Vacation are at same time, if not i should send an email stating same.
While working on this i am using "=ISNUMBER(MATCH(1,INDEX((('SUN EMEA'!C:C=A2)+('SUN EMEA'!E:E=A2))*(('SUN EMEA'!A:A>=B2)*('SUN EMEA'!A:A<=C2)+('SUN EMEA'!A:A<=B2)*('SUN EMEA'!B:B>=B2)),0),0))" where SUN EMEA is on of 7 oncall schedule i have. I have predefined roster for all so we know which sheet we have to compare the roster.
I tried writing the code however seems like i am making a mistake as i am not getting the right result when Macro is running whereas if i implement the formula it give me correct result. Below is the code i am running.
Sub vacation_check()
Dim i As Integer
For i = 300 To 303
If Cells(i, 11) = "SUN AMER" Then
Cells(i, 12) = "=ISNUMBER(MATCH(1,INDEX((('SUN AMER'!C:C= cells(i,1))+('SUN AMER'!E:E=cells(i,1)))*(('SUN AMER'!A:A>=cells(i,2))*('SUN AMER'!A:A<=cells(i,3))+('SUN AMER'!A:A<=cells(i,2))*('SUN AMER'!B:B>=cells(i,2))),0),0))"
ElseIf Cells(i, 11) = "SUN EMEA" Then
Cells(i, 12) = "=ISNUMBER(MATCH(1,INDEX((('SUN EMEA'!C:C=cells(i,1))+('SUN EMEA'!E:E=cells(i,1)))*(('SUN EMEA'!A:A>=cells(i,2))*('SUN EMEA'!A:A<=cells(i,3))+('SUN EMEA'!A:A<=cells(i,2))*('SUN EMEA'!B:B>=cells(i,2))),0),0))"
ElseIf Cells(i, 11) = "SUN APJ" Then
Cells(i, 12) = "=ISNUMBER(MATCH(1,INDEX((('SUN APJ'!C:C=cells(i,1))+('SUN APJ'!E:E=cells(i,1)))*(('SUN APJ'!A:A>=cells(i,2))*('SUN APJ'!A:A<=cells(i,3))+('SUN APJ'!A:A<=cells(i,2))*('SUN APJ'!B:B>=cells(i,2))),0),0))"
End If
Next
End Sub
Any help on this would appreciated. I have attached the file for reference.
Thanks mate, it working however Excel seems to be slowed down a lot. I mean processing is too much
It worked like charm Mate. Thanks for solution.
However at same time we wanted to look into the other sheet as well for vacation check. So i have added another column which tells in which sheet we should search for requester name.
=IF(ISERROR(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)))
I tried using this however getting error of "Too Many Argument". Any suggestion on this as we are adding condition where based on D column value we decide the sheet in which we should search.
Hello All,
I wanted 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"
As in the attached file which have 3 sheets, Vacation calendar and on call sheets. I 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.
Thanks in advance for looking into this.
