Compare two worksheets and show the differences in two other worksheets....

Hello

I am working on a project. I had it in MS Access 2003. My company decided to do away with MS Access. I have to do the same thing in MS Excel 2010.

My company has three sections 1 Hardware 2 Software 3 Accounting

And has over 6000 employees working in over 60 countries. For, this example I have used only 10 countries

36 AU Australia
156 CN China
208 DK Denmark
250 FR France
356 IN India
144 LK Sri Lanka
682 SA Saudi Arabia
376 IL Israel
826 GB United Kingdom
840 US United States Of America

Continuously people are quitting and new members are joining the company.

The EID is the only constant in each record.

I need help with this, please. I want to do it with VBA code.

First, I want to put all the members who have quit in “QuitMembers” and new members in “NewMembers”

I have highlighted the quit members in “PreviousFN” and the new members in “CurrentF”

Regards and thanks

Raghu

Hi Raghu. If I understand this correctly ...

You have a list of new & quit members that grows as staff join & leave; these are maintained in QuitMembers and NewMembers.

You have two sheets - PreviousFN and CurrentFN - with staff who have left highlighted in the PreviousFN sheet and new joiners highlighted in CurrentFN sheet.

Questions:

- Is the fact that the whole record is highlighted the only way to determine if they have left or joined?
- Will other individual cells sometimes be highlighted in records that are not joiners or leavers - as in CurrentFN? (e.g. F2, D4, B5 etc)
- Noting these two questions - would the EID ever be highlighted as in the 2nd question or can this alone be used to identify leavers / joiners?

Without putting a button on the sheets, and assuming the file is edited to have the highlighted rows of joiners / leavers, I think using the Worksheet_Deactivate event on PreviousFN & CurrentFN sheets to check for any highlighted rows (or shaded EIN if that is a valid way to ID the joiners & leavers) and copy them if they don't already exist to the QuitMembers & NewMembers sheets respectively.

Hello MatrixMan

Thank you for your response.

First of all I want to compare the worksheets and identify Quitters and New comers.

Answers to your question

- Is the fact that the whole record is highlighted the only way to determine if they have left or joined?

No the EID is unique. If a person has quit, then his or her EID will not be in the CurrentFN

- Will other individual cells sometimes be highlighted in records that are not joiners or leavers - as in CurrentFN? (e.g. F2, D4, B5 etc)

Similarly the if a new person joins, his or her EID will be absent in the PreviousFN

- Noting these two questions - would the EID ever be highlighted as in the 2nd question or can this alone be used to identify leavers / joiners?

EID is the only way to identify leavers and joiners….

Ah ... I see ... so PreviousFN and CurrentFN are actually rather large lists of all staff at the point in time they were compiled .. CurrentFN being the most recent. If that's right, then no problem and I'll write some code and send it through shortly.

See attached file. It has the code below called from the Worksheet_Activate events of the QuitMembers and NewMembers sheets.

This goes in a module:

Please Login or Register  to view this content.

And this goes in both the QuitMembers and NewMembers sheet objects:

Please Login or Register  to view this content.

Presumably the ChangedMembers one is next ... you should be able to adapt this to check each cell in the row against the previous one to update this with the same event for that sheet. Let me know if you get stuck.