+ Reply to Thread
Results 1 to 3 of 3

Mark a row if it doesn't match the row above it on multiple criteria

  1. #1
    Registered User
    Join Date
    09-26-2014
    Location
    Boston
    MS-Off Ver
    2010
    Posts
    63

    Mark a row if it doesn't match the row above it on multiple criteria

    Is there a formula that I could put in column f that will mark a cell only if all cells in the adjacent row don't match all of the cells in the row above it?

    Basically If a2,b2,c2,d2, and e2 dont match a1,b1,c1,d1, and e1 then I would like to put any sort of mark into cell f2.

    Thanks!

  2. #2
    Forum Expert
    Join Date
    02-22-2013
    Location
    London, UK
    MS-Off Ver
    Office 365
    Posts
    1,218

    Re: Mark a row if it doesn't match the row above it on multiple criteria

    In F2, try:

    =SUMPRODUCT(--(A1:E1=A2:E2))=COLUMNS(A2:E2)

    will return TRUE if all cells match, else it FALSE

  3. #3
    Forum Expert
    Join Date
    02-22-2013
    Location
    London, UK
    MS-Off Ver
    Office 365
    Posts
    1,218

    Re: Mark a row if it doesn't match the row above it on multiple criteria

    Only to mark it if no match, you could try:

    =REPT("No match",SUMPRODUCT(--(A1:E1=A2:E2))<COLUMNS(A2:E2))

    or,

    =IF(SUMPRODUCT(--(A1:E1=A2:E2))<COLUMNS(A2:E2),"No match","Match")

    and change the text string within inverted commas as needed.

+ 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] Return address of next value in list which doesn't match criteria
    By dancing-shadow in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-20-2015, 05:28 AM
  2. [SOLVED] VBA code to match and mark data from multiple sheets into a master sheet.
    By spikedemike in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-01-2015, 07:52 AM
  3. [SOLVED] Fill Cells based on 3 Criteria (2 that match and 1 that doesn't)
    By freybe06 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-21-2013, 09:40 AM
  4. [SOLVED] Mark or Delete Rows based on Matching Multiple Criteria in another worksheet
    By clearbluez in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-11-2012, 02:38 PM
  5. Replies: 6
    Last Post: 10-07-2010, 03:44 PM
  6. Formula to delete column if it doesn't match criteria
    By stekng1fan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-02-2009, 02:39 PM
  7. Display 'UNKNOWN' in a cell if the criteria doesn't match
    By famico78 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-27-2008, 09:55 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