Comparison employees between sheets

[Crispy85]

Hello,

I would like to get some help. I created an example.

What I receive are two sheets with employee information. Each employee has its own personal and unique ID which remains administrated whenever an employee leaves the company. So a number will not be re-used.

What i would like to compare between the two months is:
- Which employee left the company?
- Which employee entered the company?
- Which mutations are made in the per employee ID? (In the example for NO001 the last name has changed in the report of May, and the phonenumber of NO004 has also changed in May)

I tried to do this with vlookups and these things, however i'm having problems with the positions shifts when an employee leaves the company. So in april NO002 was in cell A3 but in May NO003 is in cell A3, because assumingly NO002 has left the company.

I was thinking maybe coloring the cells of the changed cells?

Who can help me out?

Thank you
Crispy

[oeldere]

how about this solution?

[Crispy85]

Hi Oeldere,

I found this solution myself, however i was thinking about a more automated way in the form of a macro / vba script if you know what i mean.
The ideal thing would be that it compares the ID's with the previous months, and any changes to be put in a output with coloring of the changed cells.
I can imagine that this is quite complicated....

Regards,

[oeldere]

Deleted post.

[Crispy85]

Deleted post.

[oeldere]

Add all criteria in your file, and show the forummembers how it should look like. (the result).

[Crispy85]

Thank you for the tip.
In the new example i added a sheet with Output.
It shows what's changed and colors the changed cells.

Thanks for all the help,

[Crispy85]

Bump. Especially now the other side of the world is awake

[Crispy85]

Hi oeldere,

Since there isn't really a solution for me i worked with the solution you suggested, i created a script that consolidates the 2 months and then searches for any changes in personal ID.

Thanks for the help,

Regards,
Crispy

[oeldere]

Thanks for the reply and glad I could help.

Will you mark the question solved, if it is solved.

You can add rep(utationpoints) to the members who helped you, by clicking on the star on the left side.

[Crispy85]

Hi oeldere,

I marked the thread as solved and gave you reputation.
With this solution there is still one thing left and that is the conditional formatting of the cells that have changed.

I created a new thread for this: http://www.excelforum.com/excel-gene...-question.html

Would you be so kind to take a look?

Thnx