Comparing Ranges using Conditional Formatting

vegan70

New Member
Joined
Jun 13, 2008
Messages
12
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
Joined
Sep 6, 2002
Messages
9,123
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
Joined
Jun 13, 2008
Messages
12
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 :confused:

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

Watch MrExcel Video

Forum statistics

Threads
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?

Disable AdBlock

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
Top