How to Get Detailed Data in Matching Records While Duplicates in a Specific Column Are Left Unentered

 

Problem description & analysis

Below is Excel file book1.xlsx:

undefined

undefined

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

undefined

Solution

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

A

1

C1   C2   C3…

2

ASDF1…

3

=A1.import@t()

4

=A2.import@i()

5

=A3.select(A4.pos(#1))

6

=A5.group(#1).(~.run(if(#==1,,#1=null))).conj()

Explanation:

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

A2  Copy and paste data from Sheet 2 (not including the heading).

A3  Import A1 as a table sequence.

A4  Import A2 as a sequence.

A5  Search for 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 empty 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 result in.

Q & A Collection

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