How to Transpose Many Rows into One Column

Goal

In a workbook, you might have a column called Names with about 20 names. You can transpose this column into a row to create a list of all 20 names.

Solution

• Create a SourceSheet called Names with two columns: Names and n.
• Create a FormulaSheet called NamesPrepareToGroup with a column using Names and n names, and a column using dummyGroupNo and 1.
• Create a FormulaSheet called NamesGrouped with the column NamesGrouped and the formula

  GROUPBY(#NamesPrepareToGroup!dummyGroupNo)

  the column NamesConcat with the formula

  GROUPCONCAT(#NamesPrepareToGroup!Names)

Attachements