Excel Mini Challenge

[Closed]
Report
Posts
2
Registration date
Tuesday November 13, 2012
Status
Member
Last seen
November 14, 2012
-
Posts
2819
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 25, 2021
-
Hi,

I need to compare 2 versions of the same data which reside on 2 different worksheets (within the same workbook).

On the first sheet (the master sheet), Column B is used to capture 'PurchaseDate' and on the second sheet Column D is used to capture the same data (ie 'PurchaseDate'). Each row on both sheets has a unique identifier (called 'ID') which is captured in Column A of each sheet, which can be used to create a Vlookup for comparison.

What I need is to a formula that will do all of the following:

Compare the Purchase Date on both sheets for each record

If the Purchase Date (Column B)is different on the first Sheet from the 2nd sheet
Then:
update Purchase Date (Column D) on the 2nd sheet with the value on the 1st sheet
+ colour Purchase Date (Column D) on the 2nd sheet in dark blue with white font. If the value on the first sheet is null or the record doesnt exist, then retain the value on the second sheet or replace nulls with ""
Else (if both values are the same)
Then Do nothing (ie retain the value on the 2nd sheet

I created a VLookup to find the value on the first sheet without any problem but I am stuck in a circular reference on the bit where I want it to update the value if different on sheet1 or do nothing if the value is the same on both sheets. Any help would be greatly appreciated.

Regards

Jeff

2 replies

Posts
2819
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 25, 2021
487
Hi Jeff,

A cell with a formula cannot change the contents of other cells.

This means we need to use a macro:

Sub test()
Dim lRow, cRow As Integer

Sheets("Sheet2").Select
lRow = Range("D" & Rows.Count).End(xlUp).Row

For Each cell In Range("D2:D" & lRow)
    cRow = cell.Row
    
    If cell.Value = Sheets("Sheet1").Cells(cRow, "B").Value Then GoTo Nextcell
    If Sheets("Sheet1").Cells(cRow, "B").Value = vbNullString Then GoTo Nextcell
    If cell.Value <> Sheets("Sheet1").Cells(cRow, "B").Value Then
        cell.Value = Sheets("Sheet1").Cells(cRow, "B").Value
        cell.Interior.ColorIndex = 11
        cell.Font.ColorIndex = 2
    End If

Nextcell:
Next cell

End Sub

Does this suit your needs?

Best regards,
Trowa
Posts
2
Registration date
Tuesday November 13, 2012
Status
Member
Last seen
November 14, 2012

Hi Trowa,

I hadnt thought of using macros as I thought it could be done through a formula at the cell level directly.

The macro does look like it will do what I need it to do except I dont understand how it picks up the right records on both sheets for comparison first. What I mean is let's say on sheet 1 we have records with the unique IDs 1, 2, 3, 4, 5 & 10, and on sheet 2 we have records with unique IDs 1, 2, 5, 6, 7, 8, 9 & 10 ... how will the macro pick the right records when comparing the values in Column B on sheet 1 and Column D on sheet 2? I cannot see where it is specified in the procedure for the macro to use the ID column to compare the values on the rows in each sheet.

Hope my explanation above makes sense ... if not I will still give it a try when I can and let you know the outcome ... this is a pet project currently.

Many thanks again

Jeff
Posts
2819
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 25, 2021
487
Hi Jeff,

You are right, the ID's are not in the code, since I didn't thought they were necessary.
Guess I was wrong.
So first we need to find matches of ID's and then check it's date.

Example:
Sheet1 A6 = 5
Sheet2 A4 = 5
Then compare Sheet1 B6 with Sheet2 D4.

Please comfirm that this is right (or not).

ID's are unique right?, no more then 1 match can be found.

Good to see it's just a pet project since my time is pretty limited the next two weeks.

Best regards,
Trowa