Compare text values in mutiple columns, and ignore cells

Thanks Thanks:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Compare text values in mutiple columns, and ignore cells

  1. #1
    New Member
    Join Date
    Nov 2017
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Compare text values in mutiple columns, and ignore cells

     
    Can someone please help provide a formula to comparing text values in multiple columns, for differences, and ignore blanks

    For example below:

    if the values (excluding blanks) in B,C,D,E match then F = "OK"

    if the values (excluding blanks) in B,C,D,E do not match then F = "Diffrerence"

    A B C D E F
    Item 1 BE20 BE20 OK
    Item 2 CZ28 BE20 Difference
    Item 3 BE20 BE20 BE20 GB35 Difference
    Item 4 CZ28 OK
    Item 5 CZ28 CZ28 OK
    Item 6 BE12 OK
    Item 7 BE20 BE20 BE20 OK
    Item 8 BE20 CZ28 CZ28 CZ28 Difference
    Item 9 CZ28 CZ28 OK


    Thanks in advance for any help/tips!

  2. #2
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    6,463
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compare text values in mutiple columns, and ignore cells

    "if the values (excluding blanks) in B,C,D,E match"

    What do you mean by this?
    Do you mean if the values in B C D E F on the SAME row are the same (ignoring blanks) then "OK" else "Difference"

    What result do you expect from this?

    BE20 CZ28 CZ28 BE20

  3. #3
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    42,577
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Compare text values in mutiple columns, and ignore cells

    Welcome to the Board!

    What we can do is compare the count of the total number of entries in each row to the total number of entries matching the last non-blank entry in that row (see this for details on finding the last non-blank entry in a row: https://www.extendoffice.com/documen...column.html#a1).

    So, for row 2, the formula would look like:
    Code:
    =IF(COUNTA(B2:E2)-COUNTIF(B2:E2,LOOKUP(2,1/(B2:E2<>""),B2:E2))=0,"OK","Difference")
    You can copy that formula down for all the other rows, and it should work. In my testing, it matches your expected results exactly.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  4. #4
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    6,463
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compare text values in mutiple columns, and ignore cells

    Try

    =IF((B1=C1)+(B1=D1)+(B1=E1)+1=COUNTA(B1:E1),"OK","Difference")
    and copy down the column
    Last edited by Special-K99; Nov 29th, 2017 at 11:35 AM.

  5. #5
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    42,577
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Compare text values in mutiple columns, and ignore cells

    =IF((B1=C1)+(B1=D1)+(B1=E1)+1=COUNTA(B1:E1),"OK","Diff")
    and copy down the column
    That assumes that everything must be equal to column B. So, if column B does not have an entry, it won't work.
    You can see that with example "Item 4". Your formula returns "Diff" instead of "OK".
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  6. #6
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    6,463
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compare text values in mutiple columns, and ignore cells

    Oh yep, well spotted.

  7. #7
    New Member
    Join Date
    Nov 2017
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compare text values in mutiple columns, and ignore cells

    Quote Originally Posted by Special-K99 View Post
    "if the values (excluding blanks) in B,C,D,E match"

    What do you mean by this?
    Do you mean if the values in B C D E F on the SAME row are the same (ignoring blanks) then "OK" else "Difference"

    What result do you expect from this?

    BE20 CZ28 CZ28 BE20

    Correct - if the values in B, C , D, E on the same row are the same (ignoring blanks) then 'OK' else "Difference"

    So if the any of the values are different... then "Difference"

    BE20 CZ28 CZ28 BE20 = Difference

  8. #8
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    42,577
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Compare text values in mutiple columns, and ignore cells

    Oh yep, well spotted.
    My first line of thought was your solution as well. It would be so much easier if the first column is always populated!
    Then you could just simplify my solution to:
    Code:
    =IF(COUNTA(B2:E2)-COUNTIF(B2:E2,B2)=0,"OK","Difference")
    Alas, things are seldom made that easy, four us!

    But the solution I posted just finds a cell that has a value to use in the COUNTIF, then everything works as it should.
    Last edited by Joe4; Nov 29th, 2017 at 11:51 AM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  9. #9
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    42,577
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Compare text values in mutiple columns, and ignore cells

    Gharrison, did you try the solution I posted?
    I think it should do what you are looking for.
    Last edited by Joe4; Nov 29th, 2017 at 11:52 AM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  10. #10
    New Member
    Join Date
    Nov 2017
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compare text values in mutiple columns, and ignore cells

      
    Joe4 - Thank you so much, this works perfectly! I must admit, I don't fully understand the formula, but it works, and works on different ranges (number of columns)!

    I struggled for hours earlier, but could not find a solution... so you have made my day - thanks!

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com