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

Use SPL XLL plug-in

Write the formula in a blank cell:

=spl("=E(?1).select(?2.conj().pos(#1)).group(#1).(~.run(if(#==1,,#1=null))).conj()",Sheet1!A1:E14,Sheet2!A1:A5)

As shown:

Explanation:

Search records in batches from the table sequence where the first column matches the sequence. Then group by the first column, set values of the first column (except for the first row) as null in each group, and concatenate groups.

Q & A Collection

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