Comparing values in two columns and highlighting cells that don't match

Good evening. Anyone who has ever had to manually compare two rows of data and pick out the values that don't match knows how much a pain in the butt it is. I'm hoping to automate the process and in doing so reduce the time it takes to compare values as well as decrease error rates in confirming that values match.

I've attached a workbook that contains two columns of data, a workbook that I use to "analyze" my data that I export from the web based portal as a .CSV file and a screen shot of what the data looks like in the web based portal before I export it. The attached workbook has a complete list of department codes and their corresponding earnings codes.

The first column in the workbook contains department codes which are made up of a 3 digit office location "prefix" and a 3 digit task "suffix". For example:

502600 = (502) Waterville Office (600) Travel Time

The second column contains earnings codes that are one or two word descriptors that correspond to the department codes. For example

Travel Time
Documentation
Weekly Staffing

To complicate things just a bit, some department codes are assigned only one earnings code while other department codes are assigned 5 or 6 earnings codes. For example:

503600 = (503) Wilton Office (600) Travel Time
503325 = (503) Wilton Office (325) Documentation
503325 = (503) Wilton Office (325) Non-Billable
503325 = (503) Wilton Office (325) Vacation


Here's what I'm trying to accomplish with VBA code:

1) I need the code to disregard (or delete) the first 3 digits in the earnings codes when comparing the last 3 digits with a corresponding department code.
2) I need the code to confirm that the 3 digit earnings code matches with the department code that was chosen by the employee
3) I need the cells that are mismatched to be highlighted


I'm relatively new to VBA but I'm not looking for someone to write my code for me just to give me some pointers in a direction....I'm curious and determined enough to take bits and pieces that you might be able to offer and pull them together. Thanks for taking a look.

Matthew

I appreciate your details, but I am still confused...

503600 = (503) Wilton Office (325) Travel Time

Is that a mistake?

1) I need the code to disregard (or delete) the first 3 digits in the earnings codes when comparing the last 3 digits with a corresponding department code.

In your spreadsheet the earnings codes are text, so how do you remove the first three digits? I'll assume you meant Department codes, so then you remove the first three digits and compare it to what? Seems like you could (without VBA) create a column of just the last 3 digits and then sort the table.

2) I need the code to confirm that the 3 digit earnings code matches with the department code that was chosen by the employee

Again, earnings codes appear to be text in the spreadsheet. Also, how is the department code chosen by the employee? Is there a cell reference? A form?
Sounds like you can filter a table to what the employee has chosen.

3) I need the cells that are mismatched to be highlighted

What is a mismatch. Is there an example in your spreadsheet?

I guess I'm still at square one. I see some yellow cells, are they pertinent? I looked at your macros, and they seem to reading in the data, clearing some columns, and creating tables.
In trying to glean what you are looking for, I added a column which has this formula
=MOD([@[Worked Department]],1000)
This essentially give the last three digits of 'Worked Department'. I then tried to see what the yellow cells were, so I sorted on 'Earnings Code'. I think the yellow represents your goal and that it is when there is only one instance of the three digit worked department tied to a specific earnings code. Example, there is only one 315 tied to Clinical Supervision, while there are multiple 315 tied to Client Cancellations. So, would that mean 370 tied to Client Cancellations should also be yellow, since it is unique, or am I missing something?

Pauley,

I'm off to one of my kiddo's art shows at school so I may be out of pocket for a bit but I'll respond as soon as possible once I am home. Thanks.

Matthew

BTW...for anyone who is interested in this thread...if I can tame this beast of a project, I'll post the finished product and if you find it helpful, you are free to use it.

Okay, your last post with the spreadsheet clarified my third question.

My first recommendation is to have this check done at the ADP. I would think it should have the ability to validate the person's input and have them correct it in real time rather than finding it later.

Assuming that is not possible, then you need to create a table of valid values in your worksheet. The entries would be the concatenation of your three digit code and the text, so:
461Clinical Supervision
370Collateral Contacts
325Contact Hours
385Contact Hours
...

You would then use conditional formatting with an equation something like this:
=ISNA(MATCH(MOD($B2,1000)&$D2,Range_of_your_Validation_Table,0))
and have this rule apply to $B$2:$E$405, assuming you want similar highlights in your example, but you can modify this range.