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