Anyone who has used SAP's voucher batch entry template (Excel file) knows that a voucher consists of header and multiple line items, which is a typical scenario about the association of two excel tables.

There is a problem here: when we need to enter multiple vouchers at one time, how to associate the header of each voucher with the line item?

Suppose this is an excel entry template for SAP vouchers, including multiple vouchers to be entered:

[header] Table:

[line item] Table:

After looking at the table structure, if there is a certain difference between the header text and line item text requirements in the actual business, the number field is the only one that can associate the two tables. Here [header] No. 1 and [line item] No. 1 refer to the first voucher to be entered.

When the first level loop is to traverse each row of the header table, the second level loop is to traverse each row corresponding to the voucher line item. Further decompose requirements, such as number 1. We need to dynamically calculate the starting and ending line numbers of number 1 in [line item]. There are three ideas that Xiao crawler can think of.

① dynamic calculation with excel formula;

② use two dictionaries to store the first and last line numbers of a number in [line item];

③ create a temporary table temp, quickly filter out the data with a number equal to a specific number (such as 1) by using the built-in filtering function of sql or Excel, and write it into the temporary table.

First, method 1: use match and countif to get the first and last line numbers respectively.

As shown in the figure below:

Then let's talk about method 2: use the dictionary to get the first and last line numbers of a voucher line item

We traverse each line of [line item] from top to bottom, and use the dictionary (key,value) to store the number and corresponding line number respectively. Due to the uniqueness and coverage of the dictionary [key], we always have the key corresponding to the [number], so the value always corresponds to the last position (line number) of the key, because the key is stored in the dictionary for the first time, As you traverse down line by line, it is overwritten by a new value (line number) that is not deactivated.

At this time, we only need to master a trick, first define a dictionary, traverse from top to bottom, and finally store key (number) and value (end line number); Then define a dictionary and traverse it from bottom to top to store key (number) and value (starting line number). Have you got this?

The complete implementation code example (VBA) is as follows:

 1 Sub voucherEntry()
 2 Dim headerSht As Worksheet, itemSht As Worksheet, numberStr As String, startNum As Integer, endNum As Integer, headerMaxRow As Long, itemMaxRow As Long, i As Integer, j As Integer
 3 Dim startDic As Object, endDic As Object
 4 Set startDic = CreateObject("scripting.dictionary")
 5 Set endDic = CreateObject("scripting.dictionary")
 6 
 7 Set headerSht = ThisWorkbook.Sheets("rise")
 8 Set itemSht = ThisWorkbook.Sheets("Line item")
 9 headerMaxRow = headerSht.Cells(Rows.Count, 1).End(xlUp) '[rise]The last row number of the table
10 itemMaxRow = itemSht.Cells(Rows.Count, 1).End(xlUp) '[Line item]The last row number of the table
11 For i = 2 To itemMaxRow
12     endDic.Add CStr(itemSht.Range("A" & i).Value), i 'Store the relationship between the number and the corresponding line number in the dictionary and think about why it should be converted to string format
13 Next
14 
15 For i = itemMaxRow To 2 Step -1
16     startDic.Add CStr(itemSht.Range("A" & i).Value), i 'Store the relationship between the number and the corresponding line number in the dictionary and think about why it should be converted to string format
17 Next
18 
19 For i = 2 To headerMaxRow 'Traverse each header row of the header table
20     numberStr = CStr(headerSht.Range("A" & i).Value) '[Why do you want to convert to string format
21     startNum = startDic(numberStr) 'Start line number corresponding to voucher
22     endNum = endDic(numberStr) 'End line number corresponding to voucher
23     For j = startNum To endNum 'Traverse each line of the voucher line item
24         [[code block]
25     Next
26 
27 Next
28 
29 End Sub

As for method 3, xiaoclimb will not implement this specifically for the time being. Interested students may wish to try it by themselves~

Let's check the code number and pay attention to my official account to get more knowledge about crawler and data analysis.