+ Reply to Thread
Results 1 to 10 of 10

Combining three formulas

  1. 12-17-2009, 04:58 AM #1
    neil676
    neil676 is offline
    Registered User
    Join Date
    12-17-2009
    Location
    Somerset
    MS-Off Ver
    Excel 2003
    Posts
    6

    Combining three formulas

    Hi all,

    Im sure this will be an easy one for you excel pros.

    Here are the formulas:

    =IF(ISERROR(SEARCH("*_*",A2)),0,1)

    =IF(ISERROR(SEARCH("*-*-*-*",A2)),0,1)

    =LEN(A2) - This one needs to be reworked. I use it to filter out cells with greater than 36 characters.

    So basically I would like all three formulas in one cell and if any of the three is true to display a 1 and a 0 if all are false.

    Cheers
    Reply With QuoteRegister To Reply
  2. 12-17-2009, 05:05 AM #2
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Combining three formulas

    Perhaps

    Please Login or Register  to view this content.
    My Recommended Reading:

    Volatility
    Sumproduct & Arrays
    Pivot Intro
    Email from XL - VBA & Outlook VBA
    Function Dictionary & Function Translations
    Dynamic Named Ranges
    Reply With QuoteRegister To Reply
  3. 12-17-2009, 05:50 AM #3
    neil676
    neil676 is offline
    Registered User
    Join Date
    12-17-2009
    Location
    Somerset
    MS-Off Ver
    Excel 2003
    Posts
    6

    Thumbs up Re: Combining three formulas

    Hi Donkey,

    Just what I was looking for.

    I slightly changed your formula to make it even more straight forward for the people who will be using it.

    =IF((SUM(ISNUMBER(SEARCH("_",A2)),ISNUMBER(SEARCH("-*-*-",A2)),LEN(A2)>36)>0),"Remove","Keep")

    Thanks very much for your help.

    Neil
    Reply With QuoteRegister To Reply
  4. 12-17-2009, 06:04 AM #4
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Combining three formulas

    Good stuff - FWIW you don't need the >0 test - in XL only 0 equates to FALSE all other numbers are TRUE - so the numerical output of the SUM acts as TRUE/FALSE in itself.
    Reply With QuoteRegister To Reply
  5. 12-17-2009, 06:39 AM #5
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Combining three formulas

    You could also use a single SEARCH like this

    =IF(OR(LEN(A2)>36,COUNT(SEARCH({"_","-*-*-"},A2))),"Remove","Keep")
    Reply With QuoteRegister To Reply
  6. 12-17-2009, 07:04 AM #6
    neil676
    neil676 is offline
    Registered User
    Join Date
    12-17-2009
    Location
    Somerset
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Combining three formulas

    I have decided to add to it just a little more......

    Here is what I have:

    Step 1. =IF((SUM(ISNUMBER(SEARCH("_",A2)),ISNUMBER(SEARCH("-*-*-",A2)),LEN(A2)>36)),"Copy to GAL errors","Keep")

    Now to what else that I would like to include:

    Step 2. Only run this part on cells that were not highlighted as false (keep) in step 1. =IF((SUM(ISNUMBER(SEARCH("*test*",A2)),ISNUMBER(SEARCH("*acconut*",A2)),ISNUMBER(SEARCH("*spare*",A2)),ISNUMBER(SEARCH("*test*",C2)),ISNUMBER(SEARCH("*acconut*",C2)),ISNUMBER(SEARCH("*spare*",C2)),ISNUMBER(SEARCH("*test*",D2)),ISNUMBER(SEARCH("*acconut*",D2)),ISNUMBER(SEARCH("*spare*",D2)))>0),"Needs Checking/deleting","Keep")

    The above im sure can be simplified?!!!

    Step 3. This should only be run if step 1 and step 2 both return False (Keep)

    The following three need to be combined and incorporated.

    2. =IF(ISBLANK(C8),"Missing Data?","Keep")

    3. =IF(ISBLANK(D8),"Missing Data?","Keep")

    4. =IF(ISBLANK(F8),"Missing Data?","Keep")

    Only when 2. 3. and 4. are false should "Keep" be displayed.

    Thanks again in advance.

    Neil
    Last edited by neil676; 12-17-2009 at 07:33 AM.
    Reply With QuoteRegister To Reply
  7. 12-17-2009, 07:44 AM #7
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Combining three formulas

    Quote Originally Posted by neil676
    Only run this part on cells that were not highlighted as false (keep) in step 1
    Not quite sure I follow given you state for TRUE return "Copy to GAL errors" yet latterly state "Needs Checking / Deleting" -- perhaps you meant only conduct Step 2 where Step 1 evaluates to False (not True) ?

    Regards your step 2 test...

    you could adopt daddylonglegs suggestion such that:

    Please Login or Register  to view this content.
    becomes

    Please Login or Register  to view this content.
    (is acconut a typo ?)

    Adding step 3

    Please Login or Register  to view this content.
    If you could clarify the above - ideally by means of a sample file with dummy values and expected output - we (the board) will try to resolve for you.
    Last edited by DonkeyOte; 12-17-2009 at 08:05 AM. Reason: forgot step 3
    Reply With QuoteRegister To Reply
  8. 12-17-2009, 09:05 AM #8
    neil676
    neil676 is offline
    Registered User
    Join Date
    12-17-2009
    Location
    Somerset
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Combining three formulas

    Let me try again, this time no typos and hopefully clearer. Unfortunately due to security restrictions I cannot upload any files.

    Step 1. =IF((SUM(ISNUMBER(SEARCH("_",A2)),ISNUMBER(SEARCH("-*-*-",A2)),LEN(A2)>36)),"Copy to GAL errors","Keep")

    Step 2 =IF(COUNT(SEARCH({"test","account","spare"},A2&C2&D2)),"Needs Checking/deleting","Keep")

    Step 3 =IF(SUM(COUNTBLANK(C2),COUNTBLANK(D2),COUNTBLANK(H2),COUNTBLANK(Q2)),"Missing Data?","Keep") - Might be able to condense this one?

    Tested and all three steps work as I would like. Now I need to combine all three so that the next step will only be run if the previous step returns a false statement.

    Thanks again guys and girls.

    Neil
    Reply With QuoteRegister To Reply
  9. 12-17-2009, 09:19 AM #9
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Combining three formulas

    Without knowing the content of cells C2,D2,H2 & Q2 you could perhaps try:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  10. 12-17-2009, 09:44 AM #10
    neil676
    neil676 is offline
    Registered User
    Join Date
    12-17-2009
    Location
    Somerset
    MS-Off Ver
    Excel 2003
    Posts
    6

    Thumbs up Re: Combining three formulas

    Good job!!!

    Thanks for all your help mate.

    Neil
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1