Recently, I have encountered many situations in which data needs to be synchronized between two tables. For example, there are many fields in sheet1 as the base table.

In Sheet2, there is only the following data

The information in Sheet2 is incomplete and needs to be supplemented, so VBA is needed for quick matching, but if it is not limited to these two sheets for generality, some functions are added. First create a form and add the following controls:

The three drop-down boxes are for selecting the worksheet that needs to be matched, that is, Sheet2 in Benli, and the other is used as a benchmark table, which is Sheet1 in Benli. The second benchmark field is a field that is associated when the two tables match. There is no student ID in Benli, and it needs to be a unique value.

Create a new module to store the basic method, a total of one window and one module need to be created

The algorithm in the basic function module is as follows:

Function get header array(Table Name)
     '''The function of this method is to input the table name and return the array composed of the header of the first row in the corresponding table
     '''The role of using a dictionary is to deduplicate
     'Close page refresh
    Application.ScreenUpdating = False
    Sheets(Table Name).Select
    With Sheets(Table Name)
        table row count = Sheets(Table Name).UsedRange.Rows.Count
        number of table columns = Sheets(Table Name).UsedRange.Columns.Count
        header array = Sheets(Table Name).Range(Cells(1, 1), Cells(1, number of table columns)).Value
        Dim header dictionary As Object 'Declare a dictionary object, or complete the declaration by declaring a variant variable >>> Dim d
        Set header dictionary = CreateObject("Scripting.Dictionary")    'declaration dictionary
        For i = 1 To number of table columns
            header dictionary(Sheets(Table Name).Cells(1, i) & "") = i
        Next i
        Dim array of tables()
        array of tables = Sheets(Table Name).Range(Cells(1, 1), Cells(table row count, number of table columns)).Value
        table array row count = UBound(array of tables) - LBound(array of tables) + 1
    End With
    'The purpose of removal can be achieved after conversion through a dictionary
    header array = header dictionary.Keys
    Application.ScreenUpdating = True
    get header array = header array
End Function

Function Synchronization table content(Need to synchronize table, Benchmark table, Datum field)
	'''The function of this method is to synchronize the data of the two tables, and you need to input three parameters: the table to be synchronized, the benchmark table, and the benchmark field.
    'Close page refresh
    Application.ScreenUpdating = False
    'With the selection of the worksheet, some unnecessary errors can be reduced. For example, an error will be reported if the worksheet is not selected when the array is assigned.
    Sheets(Need to synchronize table).Select
    'use with It can reduce citations, facilitate writing, and can also improve the speed slightly
    With Sheets(Need to synchronize table)
        'Get the number of rows and columns of data in a table
        Number of table rows to be synchronized = .UsedRange.Rows.Count
        Number of table columns to be synchronized = .UsedRange.Columns.Count
        'Put the header of the table to be synchronized into an array
        Need to synchronize the header array = .Range(Cells(1, 1), Cells(1, Number of table columns to be synchronized)).Value
        'Store the header content in a dictionary
        Dim Need to synchronize header dictionary As Object 'Declare a dictionary object, or complete the declaration by declaring a variant variable >>> Dim d
        Set Need to synchronize header dictionary = CreateObject("Scripting.Dictionary")    'declaration dictionary
        For i = 1 To Number of table columns to be synchronized
            Need to synchronize header dictionary(Sheets(Need to synchronize table).Cells(1, i) & "") = i
        Next i
        'Put the data in the table to be synchronized into an array
        Dim Array of tables to be synchronized()
        Array of tables to be synchronized = .Range(Cells(1, 1), Cells(Number of table rows to be synchronized, Number of table columns to be synchronized)).Value
        The number of rows in the table array to be synchronized = UBound(Array of tables to be synchronized) - LBound(Array of tables to be synchronized) + 1
    End With
    
    Sheets(Benchmark table).Select
    With Sheets(Benchmark table)
        Base table row count = Sheets(Benchmark table).UsedRange.Rows.Count
        Number of Benchmark Table Columns = Sheets(Benchmark table).UsedRange.Columns.Count
        array of benchmark headers = Sheets(Benchmark table).Range(Cells(1, 1), Cells(1, Number of Benchmark Table Columns)).Value
        Dim datum header dictionary As Object 'Declare a dictionary object, or complete the declaration by declaring a variant variable >>> Dim d
        Set datum header dictionary = CreateObject("Scripting.Dictionary")    'declaration dictionary
        For i = 1 To Number of Benchmark Table Columns
            datum header dictionary(Sheets(Benchmark table).Cells(1, i) & "") = i
        Next i
        Dim array of benchmark tables()
        array of benchmark tables = Sheets(Benchmark table).Range(Cells(1, 1), Cells(Base table row count, Number of Benchmark Table Columns)).Value
        Benchmark table array row count = UBound(array of benchmark tables) - LBound(array of benchmark tables) + 1
    End With
    
    'All the content dictionary of the header needs to be synchronized key，put into array
    Need to synchronize header dictionary keys = Need to synchronize header dictionary.Keys
    'Check equality and assignment by looping
    For i = 2 To The number of rows in the table array to be synchronized
        For j = 2 To Benchmark table array row count
            If Array of tables to be synchronized(i, Need to synchronize header dictionary(Datum field)) = array of benchmark tables(j, datum header dictionary(Datum field)) Then
                'By looping, assign values ​​to each cell of each row
                For m = 0 To UBound(Need to synchronize header dictionary keys) - 1
                    If datum header dictionary.exists(Need to synchronize header dictionary keys(m)) Then 'exists is used to determine whether a certain item exists in the dictionary kye，This method is more efficient than looping
                        Sheets(Need to synchronize table).Cells(i, Need to synchronize header dictionary(Need to synchronize header dictionary keys(m))) = array of benchmark tables(j, datum header dictionary(Need to synchronize header dictionary keys(m)))
                    End If
                Next m
            End If
        Next j
    Next i
    
    Sheets(Need to synchronize table).Select
    'Open the screen to refresh the output
    Application.ScreenUpdating = True
    'return function value
    Synchronization table content = "Data synchronization processing is complete!"
    
End Function

Read table name button method:

Private Sub read table name button_Click()
    '''Read all worksheets in the current workbook and assign them to the corresponding drop-down box controls
    'Reset dropdown box content
    ComboBox_matching table.Clear
    ComboBox_Benchmark table.Clear
    'Through the loop, get the worksheet serial number, and then put its name into the drop-down box control
    For i = 1 To Sheets.Count
        'Add content to the dropdown control
        ComboBox_matching table.AddItem (Sheets(i).Name)
        ComboBox_Benchmark table.AddItem (Sheets(i).Name)
    Next i
End Sub

After selecting the content of the requirement matching table, add the corresponding header information in the drop-down box of the benchmark field

'Drop-down box change event, this event is triggered when the content of a drop-down box changes
Private Sub ComboBox_matching table_change()
    
    header array = Basic function module.get header array(ComboBox_matching table.Text)
    'Debug.Print (ComboBox_matching table.Text & "_" & header array(0))
    header array length = UBound(header array) - LBound(header array)
    For i = 0 To header array length
        'Add content to the dropdown control
        ComboBox_Datum field.AddItem (header array(i))
    Next i
End Sub

The main function of the match content button is to call a function for calculation

Private Sub Match content button_Click()
    Need to synchronize table name = ComboBox_matching table.Text
    Base table name = ComboBox_Benchmark table.Text
    Datum field = ComboBox_Datum field.Text
    returned messages = Basic function module.Synchronization table content(Need to synchronize table name, Base table name, Datum field)
    MsgBox (returned messages)
End Sub

In summary, general data matching can be achieved, but the premise is that the header fields of the two worksheets are the same, and the order of the headers does not matter, but the names must be the same.

Sample file: https://download.csdn.net/download/huaqitaishao/12325721