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.
Name | student ID | class | specialized | gender | age | Hometown |
---|---|---|---|---|---|---|
Zhang A | 001 | one class | computer | male | 15 | Beijing |
Wang B | 002 | one class | physics | male | 30 | Shanghai |
Zhang C | 003 | one class | mining | male | 18 | Beijing |
Lee E | 004 | one class | software | male | 20 | Beijing |
Qin F | 005 | one class | Accounting | male | 26 | Beijing |
In Sheet2, there is only the following data
Name | student ID | class | specialized | gender | age | Hometown |
---|---|---|---|---|---|---|
Zhang A | 001 | |||||
Zhang C | 003 | |||||
Lee E | 004 | |||||
Qin F | 005 |
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