Problem description & analysis

Below is Excel data:

We are trying to convert each of column B and column C under the same category (column A) into a row. The desired result is as follows:

Solution

Use the SPL XLL plug-in of Excel

Write the formular in a blank cell:

=spl("=E@b(?).group@u(#1).([~.#1|~.(#2),~.#1|~.(#3)]).conj()",A1:C8)

As shown:

Explanation:

Group data by column 1(A). In each group, get the first value of column 1 and concatenate it with values of column 2 (B), and then concatenate it with values of column 3 (C), join up the concatenation results into a sequence, and then concatenate sequences into a sequence of sequences.

Q & A Collection

https://stackoverflow.com/questions/63782094/conditionally-transpose-excel-data