comparing content similarity in arrays

[ramez]

Dear experts,

In the company I'm working with , we are shifting from an old HRMS system named Legacy HRMS to Oracle HRMS system, Oracle had been running in test period for 3 months and now we have to depend 100% on it.

The attendance report in both systems must be compared, well each system can give an Excel file report , I pulled the attendance report for April 2015 from both systems and want to compare the attendance for each employee for the whole month day by day.

APRIL COMPANY'S ATTENDANCE.xls
I tried in the beginning the LOOKUP , VLOOKUP, HLOOKUP , but found that it's useless here , simply I copied the 30 days attendance from Oracle report sheet and put in beside the Legacy report in it's sheet. Well now I have 2 big arrays of all employees for 30 days, I want to find a way that excel highlight the non similar cells. so I'll bypass the similar ones and work only in the differences,

I tried the conditional formatting as below:
1- choose all the cells under (ORACLE ATTENDANCE) then: HOME -> CONDITIONAL FORMATTING -> NEW RULE -> FORMAT ONLY CELLS THAT CONTAIN -> CELL VALUES -> NOT EQUAL TO -> then choose all the cells under ( LEGACY ATTENDANCE ) -> PREVIEW -> FORMAT -> FILL -> red fill (for example) -> OK-> OK .

But I got the following error message : " You can't you a direct reference to a worksheet range in a Conditional Formatting Formula . Change the reference to a single cell, or use the reference with a worksheet function, such as =SUM(A1:E5). "

Well it didn't work & I ask for the help how can I make excel automatically compare these cells and find me the cells that not similar and highlight them in order to work on them on the Oracle attendance.

Thanks a lot in advance

[Norie]

Use a formula in the conditional formatting.

For example select AM8:BP265, goto Format>Condtional Formatting...New Rule...Use a formula to determine which cells to format, enter the formula below and select the required formatting.

Please Login or Register  to view this content.