Forum Discussion
Updating excel database with values from another sheet
I have an original database (first image) which is filled with values. I have a second database which has the same schema but only contains the changes that need to be made to the original database (second image). I am wondering in Excel if there is a way I can merge the updates into the original database while keeping the entries that don't need updating the same. I believe a Power Query merge might hold the answers but so far I can only find how to append new rows or add columns, not specifically change existing values. I would do this manually but the actual database I need to update is very large. Feel free to ask if any more details is required. The end result is shown in the image with the red text. Thanks for any help you are able to provide!
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!
5 Replies
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!
The very basic Power Query M code in the attached file returns the expected result if i correctly understand what you want to do. In the file you can add data to the blue dynamic tables. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
Hi, I think this solution ended up being very close to what I found in the end. Thank you for the suggestion and help!
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.
Cross-table references in functions or Power Query merge updates.
