Problem description

Below is a data table in Excel. Header1 field stores indexes among which there are duplicates, such as the two 101s highlighted in red boxes. The content of the table is as follows:

We are trying to search for corresponding records in the above table based a specified sequence of parameters in the order of the members. Find records corresponding to the indexes listed below in order in the above original table. The search result is null if the current index does not exist in the original table; locate the target records in their original order if any exists, and get values of Header3, Header4 and Header7 from them. Below are the indexes to be searched for:

Here is the expected result, where the search rule explanation is enclosed in red box:

Directions

1.  Open esProc to create a new file, copy data area A1:G9 in Excel to esProc’s cell A1 and data area A12:A21 to esProc’s cell B1.

2.   Write the following code:

A2   Import data in cell A1 as a table sequence.

B2   Import data in cell B1 as a table sequence.

A3   Group A2’s table sequence by Header1 for the convenience of numbering since we need to search for records according to the order of Header1.

A4   Number A3’s groups and generate a new ID field containing unique IDs.

A5   Derive a new ID field to B2’s sequence of parameters. Index the sequence members to mark their original order.

A6A7     Derive ID field in the same way.

A8   Recover the order of the table with the ID field according to Index field.

A9   Align A4’s table to A8’s sequence of parameters according to ID field.

A10 Get specified fields from the alignment result set.

3.  Run the program and copy A10’s result to paste it in B13 in Excel.

Q & A Collection

https://stackoverflow.com/questions/63804941/match-more-than-entry-according-to-the-occurrence