+ Reply to Thread
Results 1 to 8 of 8

How to get UNIQUE record higlighted

  1. #1
    Registered User
    Join Date
    12-03-2013
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    63

    How to get UNIQUE record higlighted

    Dear Friend,

    I have a report with four column and aprox ten thousand rows. The four column are COPMANY, COST CENTER, ACCOUNT and PURPOSE

    My requirement is that each row need to have a unique record. If it is not a unique record, the cell should be highlighted in ‘Red’

    For example in the table below the row 6 has a duplicate record which is same as row 3. So this row need to be highlighted in ‘Red’
    Screenshot.jpg




    Thanks,
    Rahul

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,849

    Re: How to get UNIQUE record higlighted

    You have posted in the Macros forum, but you can do this using Conditional Formatting. Do you really want a macro to do it for you?

    Pete

  3. #3
    Registered User
    Join Date
    12-03-2013
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    63

    Re: How to get UNIQUE record higlighted

    Hi Pete,

    Thanks for your quick reply. I am curious to know as how can we do this through conditional formatting?

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,849

    Re: How to get UNIQUE record higlighted

    Based on your picture, you would select all the cells that you want this to apply to from A2 down to D6, then click on Conditional Formatting | New Rule | Use a formula... , then put this formula in the dialogue box which pops up:

    =COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$C$2:$C2,$C2,$D$2:$D2,$D2)>1

    (be careful to include the $ symbols exactly as typed), then click on the Format button | Fill tab and choose red. Click OK twice to exit the dialogue box.

    I'm not sure if you want to include column D in the criteria - if not, you can omit that term.

    If you extend the range of the CF, then you might get blank rows showing red - in this case you might need another term in the formula to check that column A cells (for example) are not blank.

    Hope this helps.

    Pete

    P.S. That's off the top of my head, with no chance to try it out. If you have problems, particularly if your actual layout is different than in your picture, then attach a sample Excel workbook and I'll check it out later on (I'm going out soon).

  5. #5
    Registered User
    Join Date
    12-03-2013
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    63

    Re: How to get UNIQUE record higlighted

    Thanks Pete, your solution worked the way I wanted.

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,849

    Re: How to get UNIQUE record higlighted

    I'm glad to hear that - thanks for the rep.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    Pete

  7. #7
    Registered User
    Join Date
    12-03-2013
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    63

    Re: How to get UNIQUE record higlighted

    Hi Pete,

    Your solution worked i.e. =COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$C$2:$C2,$C2,$D$2:$D2,$D2)>1

    But just realized the some of the rows are blank/no data and these row also get highlighted with red. so just wondering how can we modify the above formula so that conditional formatting is not applied of the rows are blank.

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,485

    Re: How to get UNIQUE record higlighted

    Try: =IF($A2="",FALSE,COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$C$2:$C2,$C2,$D$2:$D2,$D2)>1)
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Unique Record
    By singh1982jeetuu in forum Excel General
    Replies: 8
    Last Post: 02-19-2015, 07:59 AM
  2. Replies: 3
    Last Post: 09-12-2013, 10:17 PM
  3. Unique Record
    By khurramfarooqpk in forum Excel General
    Replies: 14
    Last Post: 06-26-2013, 02:03 PM
  4. Replies: 4
    Last Post: 12-17-2012, 10:14 AM
  5. [SOLVED] Unique Record sum
    By LRFT in forum Excel General
    Replies: 7
    Last Post: 07-18-2012, 01:23 PM
  6. How to get the unique record from the combination?
    By kanigelpula in forum Excel General
    Replies: 1
    Last Post: 07-24-2011, 04:26 AM
  7. Unique Record
    By sumonrezadu in forum Excel General
    Replies: 4
    Last Post: 08-11-2009, 05:16 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