+ Reply to Thread
Results 1 to 4 of 4

Rows containing information that doesn't match vs rows that match. How to sort them out?

  1. #1
    Registered User
    Join Date
    08-07-2014
    Location
    Sweden
    MS-Off Ver
    2013
    Posts
    2

    Rows containing information that doesn't match vs rows that match. How to sort them out?

    Hi everyone,

    This will hopefully be an easy problem for you guys to solve, but I can't get it to work, so all help is highly appreciated!

    The problem:
    I have two columns of information (with thousands of rows) and I need to find a way to easily see which rows that has cells which doesn't match each other. I normally just mark the two columns and click "Conditional Formatting" > "Highlight Cells Rules" > "Duplicate Values" > "Duplicate values with light red fill with dark red text" > "OK", then filter out the rows which hasn't been marked.

    However, when different cells contain the same information, but on different rows, they still get marked as duplicate, so even if the information in the cells on the same row are different, they get marked as duplicate, just because another cell contains the same information.

    I'm not sure if I explained it well enough...so maybe an example will do the trick (two columns below, 9 rows):
    1. Excel - Excel
    2. Excel - Excel
    3. Windows - Paint
    4. Paint - Windows
    5. Paint - Windows
    6. Word - Excel
    7. Windows - Notepad
    8. Word - Word
    9. Word - Word

    When I use the duplicate values, it only leaves "Notepad" on row 7 as unmarked, even though I want it to leave row 3,4,5,6 and 7 unmarked, since they don't match. Any ideas how I can do that?

    Thank you in advance for the help!

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Rows containing information that doesn't match vs rows that match. How to sort them ou

    use a formula option in columm say its columns a and b
    then formula is option of =a1=b1
    copy the format down col A then use the colour filter
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Contributor
    Join Date
    08-04-2014
    Location
    Riga
    MS-Off Ver
    2010
    Posts
    579

    Re: Rows containing information that doesn't match vs rows that match. How to sort them ou

    Perhaps something like this could help. See attached.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    08-07-2014
    Location
    Sweden
    MS-Off Ver
    2013
    Posts
    2

    Re: Rows containing information that doesn't match vs rows that match. How to sort them ou

    martindwilson & bmouse - Thanks a lot, works perfectly that

+ 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] Help: Need VBA script to remove rows if a column field doesn't match a certain length.
    By rmconard in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-08-2013, 04:04 PM
  2. Formula to add rows if cell 2,1 doesn't match 3,1
    By flintcs in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-09-2013, 04:05 PM
  3. Match string in one column, return value in same row, skip rows that down't match
    By anilsen0711 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-01-2013, 05:35 AM
  4. Replies: 1
    Last Post: 10-18-2012, 04:14 PM
  5. Match similar rows from two sheets using three columns to match the "paired" rows
    By sanpedro_nz in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-04-2011, 07:15 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