Macro to Update Master Table with partial data from Source Table

[wiseone]

Hi All,

New problem I'm trying to get an idea on how to execute with VB.

I have a master table with 100 columns and 5000 rows (Table 1).

I also have a source data table in another workbook which is only 10 columns and 500 rows (TABLE 2) (which have 10 matching column headers to TABLE 1).

Both tables have a column A with unique identifiers, But table 2 may have less of them. For both tables, each column may or may not have data.

Any ideas on how to make a macro which will overwrite the cells in TABLE 1 with data from TABLE 2 with the corresponding data ONLY if the data is different than what is already in TABLE 1 and leave blank if blank?

The Master Table (TABLE1) - Holds a master list of unique identifiers and does not need to call any new identifers in (TABLE 2).

All cells must be values only.

Thanks in Advance!

Example:

MASTER TABLE - TABLE 1:

	A	B	C	D	E	F	G	H	~	DC
1	aaa	yes	10	etc
2	bbb		20
3	ccc	no	10
4	ddd	yes
5	eee	no	10
6	fff
7	ggg	yes	50
8	hhh	no	40
9	iii	yes

<tbody>
</tbody>

Source Table - TABLE2

	A	B	C	D	E	F	G	H
1	aaa		55
2	ddd	no	20
3	ggg	no	50

<tbody>
</tbody>

RESULT - Updated Master Table (TABLE 1)

A	B	C	D	E	F	G	H	~	DC
1	aaa	yes	55	etc
2	bbb		20
3	ccc	no	10
4	ddd	no	20
5	eee	no	10
6	fff
7	ggg	no	50
8	hhh	no	40
9	iii	yes

<tbody>
</tbody>

 

[wiseone]

Any thoughts on this one?

 

[wiseone]

No ideas?

 

[John_w]

See if this code works as required. Points to note:

1. Put the code in a standard module in a macro-enabled copy of your Master workbook (save as .xlsm). The code can be easily changed if you prefer the macro to run from a separate (3rd) workbook.
2. Edit the code where indicated to specify the full path and file name of the Source table workbook. That is the only change needed.
3. The Master table is expected to be in the first worksheet of the Master workbook.
4. The Source table is expected to be in the first worksheet of the Source workbook.
5. The updated Master table is written to a new worksheet in the Master workbook.
6. The code includes some validity checks (master table column heading not found or master table column A id not found) just in case there is a 'fault' in the source table data. A MsgBox is displayed if such a 'fault' is found.

Public Sub Update_Master_Table()

    Dim sourceWb As Workbook
    Dim sourceTable As Variant, sourceCol As Long, sourceRow As Long
    Dim masterTable As Variant, masterCol As Long, masterRow As Long
    Dim headingsDict As Object 'Scripting.Dictionary
    Dim newMasterWs As Worksheet
    
    'Put master table (1st worksheet) in this workbook into an array for faster processing
    
    masterTable = ThisWorkbook.Worksheets(1).Range("A1").CurrentRegion.Value
    
    'Open source workbook and put data into an array for faster processing
    
    Set sourceWb = Workbooks.Open("C:\folder\path\to\Source table workbook.xlsx")  'EDIT PATH AND FILE NAME AS NEEDED
    sourceTable = sourceWb.Worksheets(1).Range("A1").CurrentRegion.Value
    sourceWb.Close False
    
    'Create dictionary of source table column headings in row 1. A dictionary item is the source column heading and the column number
    'of the same heading in the master table
    
    Set headingsDict = CreateObject("Scripting.Dictionary") 'New Scripting.Dictionary
    
    For sourceCol = 2 To UBound(sourceTable, 2)
        
        'Find this source column heading in master table row 1
        
        masterCol = 1
        Do
            masterCol = masterCol + 1
        Loop Until masterTable(1, masterCol) = sourceTable(1, sourceCol) Or masterCol = UBound(masterTable, 2)
        If masterTable(1, masterCol) = sourceTable(1, sourceCol) Then
            headingsDict.Add sourceTable(1, sourceCol), masterCol
        Else
            MsgBox "Source table column heading '" & sourceTable(1, sourceCol) & "' not found in Master table column headings"
        End If
        
    Next
    
    'For each row in sourceTable
    
    For sourceRow = 2 To UBound(sourceTable)
    
        'Find row in masterTable where this source id matches the master id (column A)
        
        masterRow = 1
        Do
            masterRow = masterRow + 1
        Loop Until masterTable(masterRow, 1) = sourceTable(sourceRow, 1) Or masterRow = UBound(masterTable)
        
        If masterTable(masterRow, 1) = sourceTable(sourceRow, 1) Then
            
            'Source id found in master table column A.  Therefore update master table with source table values
        
            'Loop through columns in source table
            
            For sourceCol = 2 To UBound(sourceTable, 2)
            
                'Lookup column number in master table of this source column header
                
                masterCol = headingsDict.Item(sourceTable(1, sourceCol))
                
                If masterCol <> 0 Then
                
                    'Column number found. Overwrite master value if source value is not blank and master and source values are different
                
                    If Not IsEmpty(sourceTable(sourceRow, sourceCol)) And masterTable(masterRow, masterCol) <> sourceTable(sourceRow, sourceCol) Then
                        masterTable(masterRow, masterCol) = sourceTable(sourceRow, sourceCol)
                    End If
                
                End If
                
            Next
            
        Else
        
            MsgBox "Source table id '" & sourceTable(sourceRow, 1) & "' not found in Master table column A"
        
        End If
        
    Next
    
    'Add a new worksheet and write the updated master table to it
    
    Set newMasterWs = ThisWorkbook.Worksheets.Add(after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
    newMasterWs.Range("A1").Resize(UBound(masterTable, 1), UBound(masterTable, 2)).Value = masterTable
    
    MsgBox "Finished"
    
End Sub

 

[wiseone]

oooh thanks for this. Will try tomorrow. I was expecting just an explanation of what to do. Not actual code to do it. Ahahaha. Great!

 

[John_w]

There are probably some manual steps you could try to merge the two worksheets (search 'Excel merge worksheets by column and id'), e.g. primary key - Merge multiple Excel workbooks based on key column - Stack Overflow and the webpage linked from that page. However one difficulty with your scenario is the 'not blank' requirement. The 'if the data is different' requirement is superfluous since the data can be overwritten even if the master and source values are the same (no change).

 

[wiseone]

@ John,

Thank you!

Can you help some more?...

I need to append te code you provided a little, so need to understand the code a little better.

The adjustments needed are:

1. the master table column headers are in row 10, (not 1). ...where do I make the adjustment for this....and to ignore everything in rows 1-9.

2. There are 2 identifiers to match data. A SKU number and a Plant name. I need to append the code you provided to only update the master only if the two match. They are provided as such:
- In the source table, there is only the SKU number in column A. the plant name is only in the source filename (I have already extracted the plant name into a variable).
- In the master table, there is a column with a SKU number and a Column with the Plant name (neither are in column A).

Many thanks in advance if you can help with this!.....

 

[John_w]

1. the master table column headers are in row 10, (not 1). ...where do I make the adjustment for this....and to ignore everything in rows 1-9.

In this code the bold 1's mean row 1 in the Master table.

        Loop Until masterTable(1, masterCol) = sourceTable(1, sourceCol) Or masterCol = UBound(masterTable, 2)
        If masterTable(1, masterCol) = sourceTable(1, sourceCol) Then

But the masterTable array is loaded from cell A1.CurrentRegion, so there must be no empty cells between A1 and the table data - no empty cells below A1 and none to the right of A1, otherwise the CurrentRegion thing won't work. In the code row 1 is expected to be column headings and the rows below the table data.

2. There are 2 identifiers to match data. A SKU number and a Plant name. I need to append the code you provided to only update the master only if the two match. They are provided as such:
- In the source table, there is only the SKU number in column A. the plant name is only in the source filename (I have already extracted the plant name into a variable).
- In the master table, there is a column with a SKU number and a Column with the Plant name (neither are in column A).

Sorry, I don't know - it is difficult to visualise the data. May I suggest you step through the existing code with the data layout as posted in your OP and try to understand how it works.

 

[wiseone]

Hi thanks, I think I got it to work correctly by adding this:

'mastertable (masterRow,2) is referring to second column which are 'identification numbers'.
'mastertable (masterRow,3) is referring to the 3rd column which are 'Plant names'.
If masterTable(masterRow, 2) = sourceTable(sourceRow, 1) And masterTable(masterRow, 3) = PlantNames(i) Then

One thing that happened which I can't figure out how to change back, the new table which is generated does not maintain the formatting of the source data, and converts everything to numbers.

How can I maintain everything in the source formatting of the master and the source?

Thanks again!

 

[wiseone]

Now having issues with this line:

Loop Until masterTable(masterRow, 2) = sourceTable(sourceRow, 1) Or masterRow = UBound(masterTable) 
'uploadtable(uploadrow,2) is referring to second column which are materials numbers)

And getting type mismatch error.

It was working fine before, but after I saved the updated file, it didn't work anymore......thoughts?