Problem description

Below is data of Excel file book1.xlsx, where an item with same Name could have several colors.

We want to rearrange data in the file in the following layout, which is putting each Type in one row and after it each Name and the corresponding colors are listed.

The difficulty in handling the task is to list information of all records in the group and subgroups in one row after the two-level grouping.

Directions

1.     Write a script:

A1   Import data from the clipboard; @t option means reading the first row as column headers.

A2   Group data by Type and then divide each group by Name.

Loop through each group to get the Type value, join it with the sequences of Name and Color(s) obtained from the subgroups, and concatenate all members of the large sequence using conj fuction. @r option enables to compute recursively until no member is sequence.

A4   Use \t to connect values in each group (by Type) and \n to connect groups as a string and put it onto the clipboard.

2.     Select area A1:C6 in the Excel file and press Ctrl+C to copy the result string to the clipboard.

Press F9 in esProc to run the program, then back to the Excel file to click E1 and press Ctrl+V to paste the result in.

Q & A Collection  

https://stackoverflow.com/questions/63992575/referencing-data-without-duplicate-values