+ Reply to Thread
Results 1 to 9 of 9

Complicated IF function

  1. #1
    Registered User
    Join Date
    05-21-2016
    Location
    Exeter, England
    MS-Off Ver
    2013
    Posts
    35

    Complicated IF function

    Hi guys, it's me again!

    I have a little complication, here how it goes:

    I have 1000+ train routes. Previously, with the help of a certain forum member we were able to determine the starting point and end point of each route.

    Now I need to check for duplicates in a following way (please see the screenshot):
    IF cell B2 EQUALS any cell in column B (besides it's own obviously) OR any cell in column C (again, besides it's own) AND cell C EQUALS any cell in column B (besides it's own obviously) OR any cell in column C (again, besides it's own); if true - then "Yes"; if false - then "No"

    As you can see, I know what I want to achieve but I simply don't know the right combo

    Really hope someone will be kind enough to help

    I attached a spreadsheet as well for your convenience

    All the best,
    Nick
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,764

    Re: Complicated IF function

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    05-21-2016
    Location
    Exeter, England
    MS-Off Ver
    2013
    Posts
    35

    Re: Complicated IF function

    TMS,
    Awesome, tried it and it works!

    BUT as I said earlier duplicates for me are both:
    London Amsterdam vs Amsterdam London AND London Amsterdam vs London Amsterdam

    Your formula seems to work only with the first case (London Amsterdam vs Amsterdam London) while ignoring the second case.

    Could you advise me how could I have a function that accounts for both cases at once?

    And thank you once again for the first formula

  4. #4
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Complicated IF function

    I think the OP also wanted to capture duplicates in the same column (although not in the example that's how I read their explanation).

    If so you'll need to modify TMS's formula to this

    Please Login or Register  to view this content.
    Happy with my advice? Click on the * reputation button below

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,764

    Re: Complicated IF function

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,764

    Re: Complicated IF function

    @Crooza: Think you need ">1" in the second part otherwise everything will show as a duplicate.

  7. #7
    Registered User
    Join Date
    05-21-2016
    Location
    Exeter, England
    MS-Off Ver
    2013
    Posts
    35

    Re: Complicated IF function

    @TMS,@Crooza

    Guys, now it works like a charm. Really appreciate the support, thank you!

  8. #8
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Complicated IF function

    Quote Originally Posted by TMS View Post
    @Crooza: Think you need ">1" in the second part otherwise everything will show as a duplicate.
    Yeah I have a >1 there. I just put that first not last.

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,764

    Re: Complicated IF function

    @Crooza: Ah, missed that


    You're welcome.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Need Help ( Complicated IF function - Maybe- )
    By afzalzada in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-21-2015, 06:15 AM
  2. [SOLVED] Complicated IF function...
    By Janderie in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-17-2015, 08:45 PM
  3. Complicated IF function with example
    By ruthyeh in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-21-2013, 06:40 PM
  4. Complicated IF function
    By ruthyeh in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-21-2013, 10:59 AM
  5. Help with a complicated IF function
    By este994 in forum Excel General
    Replies: 17
    Last Post: 04-20-2011, 07:10 AM
  6. A Complicated Look up Function?
    By screamnyak in forum Excel General
    Replies: 2
    Last Post: 02-24-2011, 01:01 PM
  7. Complicated function; Can it be done?
    By The Boy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-30-2007, 08:10 AM

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