+ Reply to Thread
Results 1 to 7 of 7

Return TRUE/FALSE/NA when comparing between multiple columns between 2 rows

  1. #1
    Registered User
    Join Date
    09-24-2018
    Location
    Singapore
    MS-Off Ver
    2017
    Posts
    16

    Return TRUE/FALSE/NA when comparing between multiple columns between 2 rows

    Hi there. I would like to start another column to check if the condition is true, false or NA.

    The condition is when the file no. is different but both the id numbers and location tag is the same, what functions should used be used to carry that out? See example below. So, I intend Column E is the column to put the function(s).

    Screenshot 2018-10-09 at 12.35.54 PM.png

    Thank youu.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    53,209

    Re: Return TRUE/FALSE/NA when comparing between multiple columns between 2 rows

    ... but both the id numbers and location tag is the same, ...
    The same as what?

    Please explain in words the expected outcomes in your sample data.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    09-24-2018
    Location
    Singapore
    MS-Off Ver
    2017
    Posts
    16

    Re: Return TRUE/FALSE/NA when comparing between multiple columns between 2 rows

    Hi Ali

    My expected outcome based on the example above is:

    New ID number 89 is meant for equipment plate no. A5678 with the location tag side. As the old id number is -, the function (s) returns NA.

    So then due to maintenance for equipment with plate no. A5678 at the location tag side, user changed the ID number. So now the previous new ID number 89, is now the old ID number (See Column B, fourth row). The function (s) return TRUE as the ID number 89 matched with the previous old ID number with the same equipment plate no. A5678 and location tag side.

    However, user entered wrongly for the next equipment plate no. (See Row 4). Equipment plate no. A1234 also did maintenance on the location tag side but the old ID number 89 is not a match with its previous new ID number, 86 (See Column C, row 2). Therefore, the function (s) return FALSE since both the old id number and previous id number for the equipment plate no.A1234 does not match eventhough the location tag is the same side.

    Basically, the user entered the same id number 86 on two different equipments where this id number is located on the same location side.The function (s) is what I hope to be able to detect for these circulstances.

    Heehhe, hope it is understandable. Much appreciated.

  4. #4
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,277

    Re: Return TRUE/FALSE/NA when comparing between multiple columns between 2 rows

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  5. #5
    Registered User
    Join Date
    09-24-2018
    Location
    Singapore
    MS-Off Ver
    2017
    Posts
    16

    Re: Return TRUE/FALSE/NA when comparing between multiple columns between 2 rows

    Hi Samba and Ali

    Now that I've written the excel sheet, then I realised that both my previous replies were quite incorrect. Sorry for the confusion

    Attached is the excel sheet with the before and after.

    Thank you for taking a look at it.
    Attached Files Attached Files

  6. #6
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,277

    Re: Return TRUE/FALSE/NA when comparing between multiple columns between 2 rows

    F2=IFERROR(IF(LOOKUP(2,1/((A$1:A1=A2)*(D$1:D1=D2)),C$1:C1)=B2,"YES","NO"),"NA")
    Try this, copy and paste towards down

  7. #7
    Registered User
    Join Date
    09-24-2018
    Location
    Singapore
    MS-Off Ver
    2017
    Posts
    16

    Re: Return TRUE/FALSE/NA when comparing between multiple columns between 2 rows

    Hi Samba

    Yep, works exactly as per intended. Thanks for your help

+ 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. [SOLVED] Multiple ifs to return more than true or false statement
    By Vince1199s in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-08-2016, 01:12 PM
  2. Return True or False based on multiple timestamps
    By s474810 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-03-2016, 03:51 AM
  3. Return TRUE/FALSE from multiple/complex range and set BG color
    By sika85 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-24-2015, 08:48 AM
  4. [SOLVED] Comparing Multiple columns across worksheets and returning a True/False statement
    By RoryK in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-11-2015, 10:33 PM
  5. [SOLVED] Match formula to search range of columns to return True or False result
    By Twaddy006 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-09-2015, 05:42 PM
  6. Replies: 2
    Last Post: 05-26-2015, 07:29 PM

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