Excel 2007 : Compare two sheets of alpha numerical data and produce a report with the differences

Hi all

I've been landed with a huge task for a relative Excel newbie, which is trying to speed up the auditing of two systems i use at work (by speed up, i mean stop doing a manual audit!).

For some background, I have a new online system and a separate established spreadsheet, both contain the same personal data and need to match. Either can be updated independently of the other and so need to be audited each week to ensure consistency. Keep in mind, i won't know WHICH set of data is correct, I just need to be able to tell people which of their staff has inconsistent data held about them so they can sort it out.

The focus is very much on getting the new online system to match the current spreadsheet - the spreadsheets have been in use so long that everyone updates them by habit - so it is likely the online system data which will be wrong or incomplete.

The online system can export raw CSV data which i then import into Excel, and after a bit of column re-naming and formatting, i have two sheets of data which are directly comparable.

The columns i have are are text apart from Staff number:

A to G: Name, Staff number, job title, location, supplier, team manager, ops manager.

What i want to do:

1. Check the data for each cell in MasterList matches that in the ExportList
2. Copy any rows which contain cells that don't match exactly from the ExportList into a 3rd spreadsheet AuditReport
3. Highlight the cell with the inconsistent data on AuditReport in a colour so i know what needs to be investigated.

Even just comparing the data on both sheets and highlighting the cells which are inconsistent on the ExportList would be a godsend to me - i could then work with this to get the highlighted rows only and build a report from there.


I literally have no idea where to begin - i'd thought about a hlookup but i believe the data has to be numeric? Or use if then else code? (again - does this work with text?).

I don't expect someone on here to completely do my job for me... Any help would be greatly appreciated to get me started - even if its just pointing me in the direction of the Excel function i should be using and then looking at any code i come up with would be fantastic - there aren't any excel experts i can go to for help other than on somewhere like this forum.

Thanks