Compare two Sheets - Add New Data, Update Differences, Highlight Void Data

Hi All,

I would like some help generating some code to do the following:

I have two sheets in a workbook 1) "Comments", 2) "Tracker"

The "Comments" sheet is populated by another worksheet. I require the "Tracker" sheet to be populated from the "Comments" sheet which will vary and be updated itself everytime the worksheet opens or closes.
The macro needs to copy all the lines of data from "Comments" sheet to "Tracker" sheet. The data consists of the 6 columns below.

1. Item
2. Group
3. Person
4. Comment
5. Rating 1
6. Rating 2

Item & Group columns are fixed values and the remaining 4 columns vary.

The macro needs to cross-reference data in the "Comments" & "Tracker" sheets. If the row item values for Item and Group occur in both sheets, ensure, for that row that the remaining columns 3 to 6 are up-to-date (My idea was to search for the Combinations of Item & Group row data in the "Comments" sheet and if they were found in the "Tracker" sheet, copy the remaining 4 columns of data for that row and paste it behind the found Item & Group combo in the "Tracker" sheet.)

If the Item & Group values for a given row are NOT in both spreadsheets, copy the complete line from "Comments" to "Tracker".

Finally a reverse cross-reference whereby if items that have been previously copied & placed in the "Tracker" sheet are no longer present in the "Comments" sheet - Select that row of data in the "Tracker" sheet and go to the next available column and put "Void".

I have populated a sample workbook. You will see that Items 'I5' & 'I9' have been removed in the comments sheet and thus are now marked as void in the Tracker sheet.

In essence this will keep track of present and past comments, keeping current comments up to date and voiding old comments.

I realise this is quite a job but any help would be appreciated as always!
Cheers,
Mark

Hi Mark

I've some ideas; I'll play with it tonight.

John

Hi Mark

This code is in the attached workbook

Please Login or Register  to view this content.

It appears to do as requested.

Let me know of issues.

John

Hi Mark



I need some clarification.

... as a start point the Tracker document will be blank and will first have to be populated by the comment sheet including the titles in Row 1

I understand this as a first run. Then, on subsequent runs, if "Tracker" is populated, the procedure should:

1. Add new items from "Comments" to "Tracker"
2. Change "Tracker" if "Comments" has changed
3. Mark "Tracker" records "void" if no longer on "Comments"

My initial reaction is:

1. If Sheet "Tracker" doesn't exist, create it and copy Sheets "Comments" to Sheet "Tracker"
2. Run the procedure to do the Concatenation, Compare and Cross Compare.
3. On the initial run, the two Worksheets will be exactly the same.

I sense I'm missing something.

John

Hi Mark

Try the code in the attached file.

Let me know of issues.

John