Hi All
I am hoping that some one out there will be able to help me. I am looking to change a nested formula to a macro due to the amount of conditions that need to be met.
I have an export out of a system that is rather "ugly". This schedule has stuff in like holiday, Bank Holidays, Sickness and so on. I would like to take out of this just the Data if someone is off on holiday, sick, etc.
As mentioned the formula that I have is here: =IF(ISNUMBER(SEARCH("Hol 7:30",INDEX('Sorted Data'!$C$3:$NC$243,MATCH(D$1,'Sorted Data'!$A$3:$A$243,0),MATCH($B6,'Sorted Data'!$C$1:$NC$1,0)))),"HOL",IF(ISNUMBER(SEARCH(".Ho 3:45",INDEX('Sorted Data'!$C$3:$NC$243,MATCH(D$1,'Sorted Data'!$A$3:$A$243,0),MATCH($B6,'Sorted Data'!$C$1:$NC$1,0)))),"HALF",IF(ISNUMBER(SEARCH("M 7:30",INDEX('Sorted Data'!$C$3:$NC$243,MATCH(D$1,'Sorted Data'!$A$3:$A$243,0),MATCH($B6,'Sorted Data'!$C$1:$NC$1,0)))),"MAT",IF(ISNUMBER(SEARCH("CoU 7:30",INDEX('Sorted Data'!$C$3:$NC$243,MATCH(D$1,'Sorted Data'!$A$3:$A$243,0),MATCH($B6,'Sorted Data'!$C$1:$NC$1,0)))),"Comp Unpd",IF(ISNUMBER(SEARCH("SSC 7:30",INDEX('Sorted Data'!$C$3:$NC$243,MATCH(D$1,'Sorted Data'!$A$3:$A$243,0),MATCH($B6,'Sorted Data'!$C$1:$NC$1,0)))),"SICK",IF(ISNUMBER(SEARCH("CoP 7:30",INDEX('Sorted Data'!$C$3:$NC$243,MATCH(D$1,'Sorted Data'!$A$3:$A$243,0),MATCH($B6,'Sorted Data'!$C$1:$NC$1,0)))),"Comp Pd",IF(ISNUMBER(SEARCH("Dec 8:00",INDEX('Sorted Data'!$C$3:$NC$243,MATCH(D$1,'Sorted Data'!$A$3:$A$243,0),MATCH($B6,'Sorted Data'!$C$1:$NC$1,0)))),"Decline",IF(ISNUMBER(SEARCH("Dec 4:00",INDEX('Sorted Data'!$C$3:$NC$243,MATCH(D$1,'Sorted Data'!$A$3:$A$243,0),MATCH($B6,'Sorted Data'!$C$1:$NC$1,0)))),"Decline",""))))))))
I am using Excel 2016 however, the formula will need to be compatible with excel 2003 onwards. I have attached an example spreadsheet with the desired outcome and an example of the raw data that I am working with. This also has the formula in above.
I also need to need be able to add more codes to what ever solution is implemented as the codes that I have so far is not exhaustive.
I think that I have covered most things off but if you do have any questions please just ask I will be more than happy to answer.
Thank you in advance
D.
Bookmarks