Problem description & analysis

Below is Excel file book1.xlsx:

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

Write the following p1.dfx in esProc:

Explanation:

A1   Import data from the clipboard.

A2   Group data by column 1.

A3  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.

A4  Convert A3’s sequence into a string where columns are separated by tabs and rows are delimited by carriage return.

After the script is executed, select cell A4 in esProc, and click Copy data on the right. Back to Excel to click cell A10 and press Ctrl+V to paste the result in.

Q & A Collection

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