Excel Mini ChallengeClosed

Question

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

TrowaD · Answer

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

JeffAce · Answer

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

Excel Mini Challenge

2 replies

Subscribe To Our Newsletter!