Problem description & analysis

We have an Excel data:

We are trying to transpose columns to rows. Below is the expected result:

Solution

Use the SPL XLL plug-in

Write the following code in a blank cell

=spl("=E(?1).pivot@r(Name;Fruit,Fruitname).group(Fruitname).run(~=~.Fruitname|~.(Name))",Sheet1!A1:D4)

As shown:

Return:

Explanation:

Use pivot to perform column-to-row transposition, then group the table by Fruitname, get the Fruitname value for each group and concatenate it with the Name value.

Q & A Collection

https://stackoverflow.com/questions/63600495/how-do-i-make-a-new-table-where-the-rows-are-based-on-the-entries-in-another-tab