Hi All
I am hoping that someone out there will be able to help me. I am looking tochange 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 schedulehas stuff in like holiday, Bank Holidays, Sickness and so on. I would like totake 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("Hol7:30",INDEX('Sorted Data'!$C$3:$NC$243,MATCH(D$1,'SortedData'!$A$3:$A$243,0),MATCH($B6,'SortedData'!$C$1:$NC$1,0)))),"HOL",IF(ISNUMBER(SEARCH(".Ho3:45",INDEX('Sorted Data'!$C$3:$NC$243,MATCH(D$1,'Sorted Data'!$A$3:$A$243,0),MATCH($B6,'SortedData'!$C$1:$NC$1,0)))),"HALF",IF(ISNUMBER(SEARCH("M7:30",INDEX('Sorted Data'!$C$3:$NC$243,MATCH(D$1,'SortedData'!$A$3:$A$243,0),MATCH($B6,'SortedData'!$C$1:$NC$1,0)))),"MAT",IF(ISNUMBER(SEARCH("CoU7:30",INDEX('Sorted Data'!$C$3:$NC$243,MATCH(D$1,'SortedData'!$A$3:$A$243,0),MATCH($B6,'Sorted Data'!$C$1:$NC$1,0)))),"CompUnpd",IF(ISNUMBER(SEARCH("SSC 7:30",INDEX('SortedData'!$C$3:$NC$243,MATCH(D$1,'Sorted Data'!$A$3:$A$243,0),MATCH($B6,'SortedData'!$C$1:$NC$1,0)))),"SICK",IF(ISNUMBER(SEARCH("CoP7:30",INDEX('Sorted Data'!$C$3:$NC$243,MATCH(D$1,'SortedData'!$A$3:$A$243,0),MATCH($B6,'Sorted Data'!$C$1:$NC$1,0)))),"CompPd",IF(ISNUMBER(SEARCH("Dec 8:00",INDEX('SortedData'!$C$3:$NC$243,MATCH(D$1,'Sorted Data'!$A$3:$A$243,0),MATCH($B6,'SortedData'!$C$1:$NC$1,0)))),"Decline",IF(ISNUMBER(SEARCH("Dec4:00",INDEX('Sorted Data'!$C$3:$NC$243,MATCH(D$1,'SortedData'!$A$3:$A$243,0),MATCH($B6,'SortedData'!$C$1:$NC$1,0)))),"Decline",""))))))))
I am using Excel 2016 however, the formula will need to be compatible withexcel 2003 onwards. I am unable to upload an example spreadsheet with the desired outcome and an example of the raw data that I am working with.
I also need to need be able to add more codes to what ever solution isimplemented as the codes that I have so far is not exhaustive. The export some times comes out with characters before or after the code which is why I have used is search previously.
I think that I have covered most things off but if you do have any questionsplease just ask I will be more than happy to answer.
Thank you in advance
D
I am hoping that someone out there will be able to help me. I am looking tochange 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 schedulehas stuff in like holiday, Bank Holidays, Sickness and so on. I would like totake 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("Hol7:30",INDEX('Sorted Data'!$C$3:$NC$243,MATCH(D$1,'SortedData'!$A$3:$A$243,0),MATCH($B6,'SortedData'!$C$1:$NC$1,0)))),"HOL",IF(ISNUMBER(SEARCH(".Ho3:45",INDEX('Sorted Data'!$C$3:$NC$243,MATCH(D$1,'Sorted Data'!$A$3:$A$243,0),MATCH($B6,'SortedData'!$C$1:$NC$1,0)))),"HALF",IF(ISNUMBER(SEARCH("M7:30",INDEX('Sorted Data'!$C$3:$NC$243,MATCH(D$1,'SortedData'!$A$3:$A$243,0),MATCH($B6,'SortedData'!$C$1:$NC$1,0)))),"MAT",IF(ISNUMBER(SEARCH("CoU7:30",INDEX('Sorted Data'!$C$3:$NC$243,MATCH(D$1,'SortedData'!$A$3:$A$243,0),MATCH($B6,'Sorted Data'!$C$1:$NC$1,0)))),"CompUnpd",IF(ISNUMBER(SEARCH("SSC 7:30",INDEX('SortedData'!$C$3:$NC$243,MATCH(D$1,'Sorted Data'!$A$3:$A$243,0),MATCH($B6,'SortedData'!$C$1:$NC$1,0)))),"SICK",IF(ISNUMBER(SEARCH("CoP7:30",INDEX('Sorted Data'!$C$3:$NC$243,MATCH(D$1,'SortedData'!$A$3:$A$243,0),MATCH($B6,'Sorted Data'!$C$1:$NC$1,0)))),"CompPd",IF(ISNUMBER(SEARCH("Dec 8:00",INDEX('SortedData'!$C$3:$NC$243,MATCH(D$1,'Sorted Data'!$A$3:$A$243,0),MATCH($B6,'SortedData'!$C$1:$NC$1,0)))),"Decline",IF(ISNUMBER(SEARCH("Dec4:00",INDEX('Sorted Data'!$C$3:$NC$243,MATCH(D$1,'SortedData'!$A$3:$A$243,0),MATCH($B6,'SortedData'!$C$1:$NC$1,0)))),"Decline",""))))))))
I am using Excel 2016 however, the formula will need to be compatible withexcel 2003 onwards. I am unable to upload an example spreadsheet with the desired outcome and an example of the raw data that I am working with.
I also need to need be able to add more codes to what ever solution isimplemented as the codes that I have so far is not exhaustive. The export some times comes out with characters before or after the code which is why I have used is search previously.
I think that I have covered most things off but if you do have any questionsplease just ask I will be more than happy to answer.
Thank you in advance
D
