Problem description

Below is Excel data, 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.

Solution

Use the SPL XLL plug-in

Write the formular in a blank cell:

=spl("=E(?).group(Type).(~.group(Name)).((~.Type|~.(Name|~.(Color))).conj())",A1:C6)

As shown:

Explanation:

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.

Q & A Collection  

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