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

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

Hi Pete,

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

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).

Thanks Pete, your solution worked the way I wanted.

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

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.