# Comparing Ranges using Conditional Formatting

#### vegan70

##### New Member
Hi guys

Firstly - thank you for all the help you've given me - I search the message board for answers regulary but I'm stuck on this one!

I have a spreadsheet filled with raw data of employees sick leave, unfortunately, the database this comes from records each period of sickleave under each department the employee has ever worked for, creating duplicates, so I need to compare the data in row B, columns A to G, with the data in row A, Columns A to G (and so forth...) and if it's a duplicate entry, either colour the cells using conditional formatting so i can de-dup manually or just delete the duplicates?

Column A = Contract Number
Column B = Surname
Column C = First Name
Column D = Absence Start
Column E = Absence End
Column F = No of Days
Column G = Reason for Absence
Column H = Department

Any help you can offer will be much appreciated!

Many thanks

Val

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### DonkeyOte

##### MrExcel MVP
original solution provided did not work, apologies...see below but must be more efficient way

=EXACT(CONCATENATE(A2,B2,C2,D2,E2,F2,G2),CONCATENATE(A1,B1,C1,D1,E1,F1,G1))

or perhaps

=SUMPRODUCT(A2:G2)=SUMPRODUCT(A3:G3)

Last edited:

#### vegan70

##### New Member
It worked! Thank you That will save LOTS of time

You're a star x

#### vegan70

##### New Member
Sorry to bother you again LASW10, but is there a formula to create the list in the first place?

Column G has the reasons listed numerous times - I've tried the advanced filter to ask it to look at the range of cells and copy only unique values to the next sheet but I can't get it to work

Your help today has reduced a monthly task that usualy takes most of the day to a manageable chunk - thank you!

Replies
22
Views
508
Replies
0
Views
130
Replies
5
Views
447
Replies
1
Views
206
Replies
3
Views
369

1,133,243
Messages
5,657,577
Members
418,401
Latest member
B_A_M155

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back