+ Reply to Thread
Results 1 to 7 of 7

Convert a formula to a Macro

  1. #1
    Registered User
    Join Date
    05-29-2015
    Location
    Stoke-On-Trent, England
    MS-Off Ver
    2010
    Posts
    31

    Convert a formula to a Macro

    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.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    05-27-2018
    Location
    Singapore
    MS-Off Ver
    2007
    Posts
    5

    Re: Convert a formula to a Macro

    Would it help to shorten your formula if you build an extra reference table (say, in a separate tab) to list down the various conditions and their desired output?
    eg,
    CODE OUTPUT
    Hol 7:30 HOL
    .Ho 3:45 HALF
    M 7:30 MAT
    CoU 7:30 Comp Unpd
    SSC 7:30 SICK
    CoP 7:30 Comp Pd
    Dec 8:00 Decline
    Dec 4:00 Decline

    Then, in your original schedule, you may still index the employee ID and date to get the code, then vlookup to get the desired output. That way, you may simply extend on this extra reference table when new combinations arise in the future

  3. #3
    Registered User
    Join Date
    05-29-2015
    Location
    Stoke-On-Trent, England
    MS-Off Ver
    2010
    Posts
    31

    Re: Convert a formula to a Macro

    That would make sense however, I am not sure how that would look. Do you have any suggestions?

  4. #4
    Registered User
    Join Date
    05-27-2018
    Location
    Singapore
    MS-Off Ver
    2007
    Posts
    5

    Re: Convert a formula to a Macro

    Does the attached help to achieve what you need?
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-29-2015
    Location
    Stoke-On-Trent, England
    MS-Off Ver
    2010
    Posts
    31

    Re: Convert a formula to a Macro

    It kind of does however, the cell would need to parameter would need to match the sorted data sheet exactly. In some instances the cell has spaces or "," after the exception code. This would need to be replicated for the lookup to work....

    Is there a way of incorporating an issearch so that I can just find a certain string within the cell?

    Sorry to be a pain

  6. #6
    Registered User
    Join Date
    05-29-2015
    Location
    Stoke-On-Trent, England
    MS-Off Ver
    2010
    Posts
    31

    Re: Convert a formula to a Macro

    Hi All

    I have still been unable to find a solution to the above. Does any one have any other suggestions? Or can any one point me in the right direction?

    Thanks again

    Dominic

  7. #7
    Registered User
    Join Date
    05-27-2018
    Location
    Singapore
    MS-Off Ver
    2007
    Posts
    5

    Re: Convert a formula to a Macro

    Quote Originally Posted by Dominic.Brice View Post
    It kind of does however, the cell would need to parameter would need to match the sorted data sheet exactly. In some instances the cell has spaces or "," after the exception code. This would need to be replicated for the lookup to work....

    Is there a way of incorporating an issearch so that I can just find a certain string within the cell?

    Sorry to be a pain
    Yes, should the reference data be mixed with trailing spaces or "," or "."... the lookup will not work...

    Im not sure if there is a more elegant way to resolve your problem (other than ensuring the user inputs the data correctly), but below is a function code which will eliminate the trailing spaces and "." and "," before passing it as a vlookup to get the output

    In short, the function works this way = SearchA(cell with the employee ID, cell with the date). This function works just like the previous method without VBA, excepts it removes the trailing spaces and "." before looking up the parameter list

    Again, simply extend the PARAMETER list should you have a new code

    Please Login or Register  to view this content.
    Thanks!
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1