review data for changes and then paste changed info into sheet

Hi, I’m using Excel 2010. I have a report which I need to update on a weekly basis. Each row in the spreadsheet contains one reference number and some other associated information (eg the date the ref was created, current status etc) .   When I receive the weekly report, I need to update the data in the spreadsheet to show any changes in status and any new reference numbers that have been created.    Rather than me examining every item to see if the data has changed, is it possible to write a formula that will look at the reference number in A1, and if that number occurs in the new report, to paste the entire row from the “new” report into the ”old” report. Then do the same with A2, then A3 etc .       Also, if any new reference numbers are added these need to be added to the bottom of the spreadsheet.     Is this too complicated to do using formulas or will  I need to learn VB  or something to do this?

3 thoughts on “review data for changes and then paste changed info into sheet

  1. Excel Geek

    Hi there

    Try this piece of VBA code… You will need to make some changes depending on how many columns you have. You will also need to change the references to sheet names. I have used “Current Data” and “New Data”. Please do not test this on your actual data until you are happy it works. The code is provided as is.

    Sub Update_Data()

    'Declare the array
    Dim Data() As Variant

    'Select the sheet containing new data
    Sheets("New Data").Select

    'Count how many rows of data there are
    DataCount = Cells(Rows.Count, 1).End(xlUp).Row

    'Loads new data into an array
    Data() = Range(Cells(1, 1), Cells(DataCount, 5)).Value

    'Select the worksheet that contains current data
    Sheets("Current Data").Select

    'Count how many rows of data there are
    DataCount = Cells(Rows.Count, 1).End(xlUp).Row

    'Replace data that matches
    For X = 1 To DataCount Step 1
    For Y = 1 To UBound(Data) Step 1

    If Cells(X, 1) = Data(Y, 1) Then
    i = 1 'This represents column A
    Do Until i = 6 'Change this number to the number of columns you have + 1
    Cells(X, i) = Data(Y, i) 'This replaces the current data with the new data
    i = i + 1
    Loop
    Exit For
    End If

    Next Y
    Next X

    'Enter New Lines of Data not already recorded
    For X = 1 To UBound(Data) Step 1
    For Y = 1 To DataCount Step 1

    If Data(X, 1) = Cells(Y, 1) Then GoTo NextX 'If matches then skip

    If Data(X, 1) Cells(Y, 1) Then GoTo NextY 'If no match check next line
    NextY:

    'If there has been no match then enter new line of data
    If Y = DataCount Then

    i = 1 'This represents column A
    NewLine = 1
    Do Until i = 6 'Change this number to the number of columns you have + 1
    Cells(Y + NewLine, i) = Data(X, i) 'This replaces the current data with the new data
    i = i + 1
    Loop
    NewLine = NewLine + 1
    DataCount = DataCount + 1
    Exit For

    End If
    Next Y

    NextX:
    Next X

    End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *