+ Reply to Thread
Results 1 to 6 of 6

Conditional Formatting: Find When Values in 3 Columns Are Repeated in Rows

  1. #1
    Forum Contributor
    Join Date
    10-08-2012
    Location
    San Clemente, California
    MS-Off Ver
    Office365
    Posts
    383

    Conditional Formatting: Find When Values in 3 Columns Are Repeated in Rows

    This seemed simple a few hours ago.

    A log of invoice details MAY contain duplicate entries. If the columns for INVOICE and DATE and AMOUNT on one row are repeated on another row, the duplicates need to be highlighted. Highlight can be applied to any or all three columns or even the entire row.

    Duplicates in any one of the three columns is common (even Invoice number). It is when the same value appears in all three columns in multiple rows that a duplicate invoice has been entered and highlighting is needed.

    Alternatively, I could use an IF and COUNTIFS formula but that would take up an extra column, but I would prefer conditional formatting if possible...or possibly a VBA solution.

    Thanks for your time.

  2. #2
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Conditional Formatting: Find When Values in 3 Columns Are Repeated in Rows

    For a CF formula you could try something like this:
    (Assuming A2:A1000 = Invoice; B2:B1000 = Date; C2:C1000 = Amount)
    Select Range A2:C1000
    and use this formula in the CF,New Rule,Use Formula :
    =COUNTIFS($A$2:$A$1000,A2,$B$2:$B$1000,B2,$C$2:$C$1000,C2)>1
    Format,Fill...select color,OK,OK

    Hope this helps

    EDIT-
    For a large set of data, this may slow down processing time when you make changes to your workbook...
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  3. #3
    Forum Contributor
    Join Date
    10-08-2012
    Location
    San Clemente, California
    MS-Off Ver
    Office365
    Posts
    383

    Re: Conditional Formatting: Find When Values in 3 Columns Are Repeated in Rows

    Hi dredwolf,

    Your suggestion works perfectly! I can only make it format the first column. It would be nice to highlight all three elements or the whole row but I think I may be pushing my luck!

    thanks again for the speedy and most helpful response.

    Best regards,
    tom

  4. #4
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Conditional Formatting: Find When Values in 3 Columns Are Repeated in Rows

    aah, Maybe this then:
    =COUNTIFS($A$2:$A$1000,$A2,$B$2:$B$1000,$B2,$C$2:$C$1000,$C2)>1

    that should highlight all 3 elements, I missed the "$" in the original solution, sorry about that

    Hope that helps

  5. #5
    Forum Contributor
    Join Date
    10-08-2012
    Location
    San Clemente, California
    MS-Off Ver
    Office365
    Posts
    383

    Re: Conditional Formatting: Find When Values in 3 Columns Are Repeated in Rows

    Aha! That highlights the entire row...perfect. Thanks again, dredwolf! tom

  6. #6
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Conditional Formatting: Find When Values in 3 Columns Are Repeated in Rows

    You are welcome

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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