Info comparison in Excel 2

[newguy86]

First thing I wanted to say was sorry for the long post. The process that I am trying to complete is a little complicated.

Here is the situation, I have an Excel file that has three sheets in it where information is inputted. And I am wanting to do a few things with the information.

Sheet 1-Start in Cell B2 and search through all of sheet 2 for a match to the contents of Cell B2 in Sheet 1. If a match is found then move on to the next cell in the sheet. If no match is found then change Cell B2 in sheet 1 to yellow. Repeat process for sheet 3. But if no match is found then change the cell color to bright green or if the cell is already yellow from sheet 2 then change the color in the cell to turquoise.

Sheet 2-Start in Cell B2 and search through all of sheet 1 for a match to the contents of Cell B2 in Sheet 2. If a match is found then move on to the next cell in the sheet. If no match is found then change Cell B2 in sheet 2 to red. Repeat process for sheet 3. But if no match is found then change the cell color to bright green or if the cell is already red from sheet 1 then change the color in the cell to turquoise.

Sheet 3-Start in Cell A2 and search through all of sheet 1 for a match to the contents of Cell A2 in Sheet 3. If a match is found then move on to the next cell in the sheet. If no match is found then change Cell A2 in sheet 3 to red. Repeat process for sheet 2. But if no match is found then change the cell color to yellow or if the cell is already red from sheet 1 then change the color in the cell to turquoise.

So basically speaking I am wanting to compare the contents of each sheet with the other sheets to see what sources are missing what information. And the process for each sheet is pretty much the same but with formatting differences. Below are examples of the different sheets.

Sheet 1
Location Code Description
xxx1 xxxc01
xxx2 xxxc02
xxx3 xxxc03


Sheet 2
Location Code Description1 Description2 Description3 Description4
xxx1 xxxxxx xxxc01 xxxc01 xxxc01
xxx2 xxxc02 xxxc02 xxxc02 xxxc02
xxx3 xxxc03 xxxc03 xxxc03 xxxc03


Sheet 3
Description1 Description2 Description3 Description4
xxxxxx xxxc01 xxxc01 xxxc01
xxxc02 xxxc02 xxxc02 xxxc02
xxxc03 xxxc03 xxxc03 xxxc03


Any assistance would be greatly appreciated.

Thanks

Travis

http://www.enterprise.com

 

[xlbo]

This would require VBA

Please post in Forum707 and be prepared to show what you have tried and what doesn;t work

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

I agree with xlbo, that this is problem must be solved using VBA code rather than spreadsheet features.

However, you need to very carefully and methodically record your exact requirements. Think, for instance, of the fact that when you compare a value with another value that you should never have to compare another value with a value, because you ALREADY compared a value with another value. So ALL your compares ought to be FORWARD-looking.

Also you have specified some color coding. It seems to me that you have not been complete with ALL the conditions that might occur.

So please carefully review your logic and specification and please be CLEAR, CONCISE and COMPLETE.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[newguy86]

I was thinking of writing it in VBA. I just didn't know if there was a pre-programmed setup that would complete this for me.

Thanks and I will give VBA a try.

Travis

http://www.enterprise.com