Problem description & analysis

Below is Excel file book1.xlsx:

The row-wise worksheet Sheet1 stores the source data, in which C1-C5 are column names. C1 is the categorizing column and data is already ordered by it. Sheet 2 contains sequence-type parameters that correspond to C1 in Sheet 1. The task is to get C1-C5 columns from Sheet 1 according to the parameter in order to generate a new worksheet. For column C1, only the first row of each category will be retained and other rows will be left empty, as shown below:

Solution & explanation

We write the following script (p1.dfx) in esProc:

Explanation:

A1   Copy and paste data from Sheet 1 (including the table header).

A2  Copy and paste data from Sheet 2 (excluding the table header).

A3  Import A1 as a table sequence.

A4  Import A2 as a sequence.

A5  Search records in batches from the table sequence where the first column matches the sequence.

A6  Group A5 by the first column, set values of the first column (except for the first row) as null in each group, and concatenate groups.

After the program is executed, select cell A6 in esProc and click “Copy data” button on the right. Then back to Sheet 2 to click cell A2 and press Ctrl+V to paste the result in.

Q & A Collection

https://stackoverflow.com/questions/64229062/vba-selecting-multiple-values-from-one-sheet-based-on-input-of-another-sheet