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?

## You might also find helpful in Excel...

Hi

Do you want the rows overwritten if a matching reference number is found?

Yes.

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`