+ Reply to Thread
Results 1 to 7 of 7

If statement with a range and true/false statement

  1. #1
    Registered User
    Join Date
    04-19-2012
    Location
    Toronto
    MS-Off Ver
    Excel 2007
    Posts
    3

    If statement with a range and true/false statement

    Hi guys,

    First time poster. I have a problem that I think is somewhat complicated. The most difficult thing that I have tried to do with excel. I hope that you guys will be able to help. I have two different reports that I want to compare data on. One report is a master list of numbered accounts and a manager for each account. The other will be a list of transactions for those accounts that will also have an accompanied manager. I want to make sure that the manager matched on the transactions report is the correct manager from the master list. I am hoping to do an if statement that will have an equals sign in it to give me a true/false output. I have devised the following formula, but I think I'm pretty far off. I am thinking another possibility would be an embedded if statement but I am unsure how to do this as well. For simplicity, I have written the formula with the values all on the same sheet.

    =IF(A25:A31=E25:E31,C25:C31=G25:G31, UNKNOWN)

  2. #2
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: If statement with a range and true/false statement

    Hello and welcome to the forum, Could you please upload a sample workbook so we can see your data structure? You can use false data, but keep the structure the same
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

  3. #3
    Registered User
    Join Date
    04-19-2012
    Location
    Toronto
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: If statement with a range and true/false statement

    OK, I have attached an example of what I will be dealing with. The only difference is that rather than dealing with 2 sheets, I will be dealing with 2 workbooks but that is not really an issue. You can see that I got a true statement for the few transactions that are listed in the same position on the 2 reports. For my purposes, this will not happen as my reports will both use thousands of accounts that will be in a random order. Hope you can help.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: If statement with a range and true/false statement

    ... Updated post ...

    This will check the SO # against the Master worksheet and if it find it, then it will check Mangers to make sure they are the same. Three results are possible:

    1) #NA - SO # not found in Master sheet
    2) True - SO # and Managers match (second sheet needs to be ordered in alphanumeric order, and will only catch the first SO # so can't have multiple SO # with different managers in different rows).
    3) False - SO # and Managers don't match

    Hope this helps.

    abousetta
    Last edited by abousetta; 04-20-2012 at 12:37 PM. Reason: Updated post

  5. #5
    Registered User
    Join Date
    04-19-2012
    Location
    Toronto
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: If statement with a range and true/false statement

    Quote Originally Posted by abousetta View Post
    ... Updated post ...

    This will check the SO # against the Master worksheet and if it find it, then it will check Mangers to make sure they are the same. Three results are possible:

    1) #NA - SO # not found in Master sheet
    2) True - SO # and Managers match (second sheet needs to be ordered in alphanumeric order, and will only catch the first SO # so can't have multiple SO # with different managers in different rows).
    3) False - SO # and Managers don't match

    Hope this helps.

    abousetta
    You did not provide any formula lol?? In regards to what you had mentioned, when you refer to the second report you mean the actual bookings report? If that is what you are referring to, I can sort alphanumerically, but the SO# is going to be the number of the parent account, so in a given period there is often going to be multiple transactions for a parent account. Also, some of those transactions will be allocated to the correct manager and some won't, so I need a formula that finds only those that are allocated to the wrong manager. As I mentioned, this is a fairly difficult problem, but if I am able to solve it, I will save my organization thousands of man hours, and thus dollars. Thanks for the help and I hope that together we can solve this.

  6. #6
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: If statement with a range and true/false statement

    Here's the formula:

    Please Login or Register  to view this content.
    for row 2 and drag down.

    But reading your last post, it needs to be modified. Will be back soon (I hope)

  7. #7
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: If statement with a range and true/false statement

    OK. I relooked at your workbook. Put the forumla I gave you in post #6 in the actual bookings report (transactions worksheet). You will need to sort the parent account in aphanumeric order.

    Let me know if you need any further adjustments.

    abousetta

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1