+ Reply to Thread
Results 1 to 14 of 14

Comparing two rows for exact match

  1. #1
    Registered User
    Join Date
    11-19-2018
    Location
    Richmond, VA
    MS-Off Ver
    Windows 10
    Posts
    7

    Comparing two rows for exact match

    Hello,

    I am comparing two rows cell by cell for exact match. I am having trouble because some cells are blank, however if both are blank, that is a match. Can someone help me with a formula to review that E4=T4, F4=U4, and so on. I am looking for it to say, "Match" if they match and "Review if they do not.

    Example
    Comparing E4:G4 to T4:V4
    E4 F4 G4
    Yes Yes
    T4 U4 V4
    Yes Yes

  2. #2
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Comparing two rows for exact match

    Hi

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

  3. #3
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Comparing two rows for exact match

    in E5
    =IF(E4=T4,"Match","Review")
    copy across to G5
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  4. #4
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Comparing two rows for exact match

    Try: =if(or(and(E4="",T4<>""),and(E4<>"",T4=""),E4<>T4),"No match", if(and(E4="",T4=""),"Both Empty","Match"))
    Click the * to say thanks.

  5. #5
    Registered User
    Join Date
    11-19-2018
    Location
    Richmond, VA
    MS-Off Ver
    Windows 10
    Posts
    7

    Re: Comparing two rows for exact match

    I still get review if the two cells being compared are both blank, this would be a match

  6. #6
    Registered User
    Join Date
    11-19-2018
    Location
    Richmond, VA
    MS-Off Ver
    Windows 10
    Posts
    7

    Re: Comparing two rows for exact match

    I still get a "Review" if both cells are blanks that are being compared, this would be a match

  7. #7
    Registered User
    Join Date
    11-19-2018
    Location
    Richmond, VA
    MS-Off Ver
    Windows 10
    Posts
    7

    Re: Comparing two rows for exact match

    I still get a review for blank cells that match

  8. #8
    Registered User
    Join Date
    11-19-2018
    Location
    Richmond, VA
    MS-Off Ver
    Windows 10
    Posts
    7

    Re: Comparing two rows for exact match

    Quote Originally Posted by dhoath View Post
    I still get review if the two cells being compared are both blank, this would be a match
    Quote Originally Posted by PaulM100 View Post
    Try: =if(or(and(E4="",T4<>""),and(E4<>"",T4=""),E4<>T4),"No match", if(and(E4="",T4=""),"Both Empty","Match"))
    Two blank cells give me "No Match" - these cells are blank as a result of a formula that had no information, I am not sure if that could be it

  9. #9
    Registered User
    Join Date
    11-19-2018
    Location
    Richmond, VA
    MS-Off Ver
    Windows 10
    Posts
    7

    Re: Comparing two rows for exact match

    Quote Originally Posted by dhoath View Post
    I still get review if the two cells being compared are both blank, this would be a match
    Quote Originally Posted by José Augusto View Post
    Hi

    Try
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I still get a review for two blank cells

  10. #10
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Comparing two rows for exact match

    What about my solution, post #3 ?

  11. #11
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Comparing two rows for exact match

    FWIW, if X and x are not equal you should likely use EXACT - e.g. (adapting post#2):

    =IF(AND(INDEX(EXACT(E4:G4,T4:V4),0)),"Match","Review")

  12. #12
    Registered User
    Join Date
    11-19-2018
    Location
    Richmond, VA
    MS-Off Ver
    Windows 10
    Posts
    7

    Re: Comparing two rows for exact match

    Quote Originally Posted by dhoath View Post
    I still get review if the two cells being compared are both blank, this would be a match
    Quote Originally Posted by Special-K View Post
    in E5
    =IF(E4=T4,"Match","Review")
    copy across to G5
    Gives me review if two cells are blank

  13. #13
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Comparing two rows for exact match

    I would imagine one, or both, aren't really blank and contain spaces / other characters

    what does =LEN(E4)-LEN(T4) return?

  14. #14
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Comparing two rows for exact match

    Quote Originally Posted by dhoath View Post
    Gives me review if two cells are blank
    I agree with XLent the cells are different.

    1. Open a new sheet
    2. Enter the formula in E5
    3. Copy across to G5

    The result is MATCH for all three cells, since ALL the cells are blank, they must be, you've just created a new sheet.
    If you dont get this then your cells are not blank, even though they look like it.

+ 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. Replies: 1
    Last Post: 03-25-2019, 12:24 PM
  2. Comparing two lists on non-exact match
    By Martinio in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-08-2017, 04:01 PM
  3. [SOLVED] Match non-exact text and manipulate data to different rows and columns
    By ast58 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-19-2015, 08:42 PM
  4. To Sort exact and partial exact match for a single column.
    By Jagdev in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-22-2014, 05:08 AM
  5. [SOLVED] Find exact match in multiple columns in 1 row with all other rows 90,000 rows.
    By KHurt in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-02-2013, 01:38 PM
  6. Comparing two text columns and return data based on an exact match
    By AShah33 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-04-2013, 04:23 PM
  7. Comparing text from two different rows that are Almost the same, but not exact
    By ajr2183 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-22-2012, 02:46 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