How to sort columns in Excel so that only one value appears in each row?

Question

Say I have a a spreadsheet with the following data:

 A     B     C     D
---------------------
 2     1     0     2
 3     2     1     4
 5     3     3     6
 7     5     6     8
11     8    10    10
13    13    15    12
17    21    21    14
19    34    28    16
23    55    36    18
29    89    45    20

I want to sort the columns so that each row only contains a single value like this:

 A     B     C     D
---------------------
             0
       1     1
 2     2           2
 3     3     3
                   4
 5     5
             6     6
 7
       8           8
            10    10
11
                  12
13    13
                  14
            15
                  16
17
                  18
19
                  20
      21    21
23
            28
29
      34
            36
            45
      55
      89

Is there a quick way to do this in Excel?

Answer 1

Your questions asks for a worksheet function, I don't think it's possible so I offer a macro (VBa) which should do what you want!

Option Explicit
Sub DoThis()

Dim row As Integer
row = 1

Dim col As Integer
col = 65

    Do While (Range("A" & row).Value <> "")

        Do While (Range(Chr(col) & row).Value <> "")

        Dim currentValue As Integer
        currentValue = Range(Chr(col) & row).Value

        Range(Chr(col + 7) & currentValue + 1).Value = currentValue

        col = col + 1
        Loop

        row = row + 1
        col = 65

    Loop        

End Sub

The Excel sheet I used (I only used the first 4 rows or your example due to time restraints)

And after I run it

As you can see, I've chosen to save the new table in a different place so it's non destructive.

Answer 2

would resolve this with the Power Query Add-In. It takes a few steps to get there and a bit of coding in the Power Query language (M) to call the Table.Pivot function (it's not exposed in the Power Query UI). But that's 1 line of code (the rest was built by clicking around in the Power Query UI), vs around 20 lines of VB code.

I've built a prototype which you can view or download - its "Power Query demo - Sort and spread rows by value.xlsx" in my One Drive:

https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398

Basically my technique was to Unpivot the columns into rows, then copy the Value (representing each cell). Then I used the Table.Pivot function to return the data into the A,B,C,D columns. Then I added a Group By and Sort using the "Copy of Value" column to achieve the final result.

The documentation for Table.Pivot is here:

http://office.microsoft.com/en-au/excel-help/table-pivot-HA104111995.aspx?CTT=5&origin=HA104122363

Another example of using Table.Pivot is here:

http://cwebbbi.wordpress.com/2013/11/25/pivoting-data-in-power-query/