Forum Discussion
Updating excel database with values from another sheet
Hi all, I did end up finding a solution to the above problem using power query and the merge queries based on the ID. I then created new columns from the merge and used code along these lines: if [Updates.Age] = null then [Age] else [Updates.Age] where [Age] were the original dataset values and [Updates.Age] were the updating values. That way if there was no value in the updating dataset it would default to the original. I then deleted the original and updating columns and renamed the new columns to the original attribute names. So far it seems to be working well. Thank you all for your comments though, I really appreciate it!
In the desktop version of Excel for Windows/Mac you can use a macro:
Sub UpdateData()
Const headerRow = 2
Const idCol = 2
Dim wsd As Worksheet
Dim wsc As Worksheet
Dim r As Long
Dim lastRow As Long
Dim c As Long
Dim lastCol As Long
Dim rng As Range
Dim s As Long
Dim n As Long
Application.ScreenUpdating = False
' Data Sheet
Set wsd = Worksheets("Data Sheet")
' Changes Sheet
Set wsc = Worksheets("Changes Sheet")
' Last row of changes
lastRow = wsc.Cells(wsc.Rows.Count, idCol).End(xlUp).Row
' Last column of changes
lastCol = wsc.Cells(headerRow, wsc.Columns.Count).End(xlToLeft).Column
' Loop through IDs on Changes Sheet
For r = headerRow + 1 To lastRow
' Find ID on Data Sheet
Set rng = wsd.Columns(idCol).Find(What:=wsc.Cells(r, idCol).Value, LookAt:=xlWhole)
If Not rng Is Nothing Then
s = rng.Row
For c = idCol + 1 To lastCol
If wsc.Cells(r, c).Value <> "" Then
If wsd.Cells(s, c).Value <> wsc.Cells(r, c).Value Then
wsd.Cells(s, c).Value = wsc.Cells(r, c).Value
n = n + 1
End If
End If
Next c
End If
Next r
Application.ScreenUpdating = True
MsgBox "Applied " & n & " change(s)", vbInformation
End SubSee the attached sample workbook. You'll have to allow macros.