How to use macros to sort data in multiple columns as one column in Excel

Article translations Article translations
Article ID: 247311 - View products that this article applies to.
This article was previously published under Q247311
Expand all | Collapse all

On This Page

SUMMARY

Microsoft Excel does not have a built-in method to sort a newspaper (snaking) column of data. This article contains a sample macro to sort data that is in a newspaper column format.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

Built-in sorting

In Microsoft Excel, you usually use the sorting feature to sort a list. A list in Excel is a series of worksheet rows that contain related data, such as an invoice database or a set of client names and phone numbers. You can use a list as a database, in which each row is a record, and the columns are fields. The first row of the list usually has labels for the columns, for example:
   A1: Q1   B1: Q2   C1: Q3
   A2: 10   B2: 2    C2: 4
   A3: 14   B3: 18   C3: 9
   A4: 7    B4: 17   C4: 12
   A5: 1    B5: 13   C5: 5
   A6: 11   B6: 8    C6: 16
   A7: 3    B7: 6    C7: 15
				
If you sorted this list in ascending order, by the Q1 field (column A), the results would be as follows:
   A1: Q1   B1: Q2   C1: Q3
   A2: 1    B2: 13   C2: 5
   A3: 3    B3: 6    C3: 15
   A4: 7    B4: 17   C4: 12
   A5: 10   B5: 2    C5: 4
   A6: 11   B6: 8    C6: 16
   A7: 14   B7: 18   C7: 9
				

Sorting a newspaper-style column

Newspaper columns and snaking columns are different terms for the same type of data format. In newspaper columns, the data fills one column and continues at the top of the next column. In this case, your data probably does not have labels for each column, for example:
   A1: 10   B1: 2    C1: 4
   A2: 14   B2: 18   C2: 9
   A3: 7    B3: 17   C3: 12
   A4: 1    B4: 13   C4: 5
   A5: 11   B5: 8    C5: 16
   A6: 3    B6: 6    C6: 15
				
You can use the sample macro in this article to sort newspaper columns in ascending order. If you use the sample macro on the above data, the results are as follows:
   A1: 1    B1: 7    C1: 13
   A2: 2    B2: 8    C2: 14
   A3: 3    B3: 9    C3: 15
   A4: 4    B4: 10   C4: 16
   A5: 5    B5: 11   C5: 17
   A6: 6    B6: 12   C6: 18
				
To see the sorting macro work as described, follow these steps:
  1. Open a new workbook.
  2. Type the following sample data in the worksheet:
       A1: 10   B1: 2    C1: 4
       A2: 14   B2: 18   C2: 9
       A3: 7    B3: 17   C3: 12
       A4: 1    B4: 13   C4: 5
       A5: 11   B5: 8    C5: 16
       A6: 3    B6: 6    C6: 15
    					
  3. On the Tools menu, point to Macro, and then click Visual Basic Editor.
  4. On the Insert menu, click Module.
  5. Type the following code in the module:

    Note The following example demonstrates how to sort in ascending order. You can change this by modifying the value of the "Order1:=" argument to xlDescending.
       Sub SortAllRangeData()
       ' Place column header for temporary sort area.
       Range("IV1").Value = "Numbers"
       
       ' Move numbers to temporary sort location.
       For Each cell In Selection
          Range("iv65536").End(xlUp).Offset(1, 0) = cell.Value
       Next cell
       
       ' Sort numbers in ascending order.
       Range("IV1", Range("IV1").End(xlDown)).Sort Key1:=Range("IV2"),  _
       Order1:=xlAscending, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
            
       ' Move sorted data back to original sheet location.
    
       Selection(1, 1).Activate ' Make sure the ActiveCell is the
                                ' top left of Selection first.
       CCnt = Selection.Columns.Count
       RCnt = Selection.Rows.Count
       CellCnt = Selection.Cells.Count
       Tcell = 2
       For c = 1 To CCnt
         For r = 1 To RCnt
            Range(ActiveCell.Address).Offset(r - 1, c - 1).Value =  _
            Range("iv" & Tcell).Value
            Tcell = Tcell + 1
         Next r
       Next c
       
       ' Clean up temporary sort location.
       Range("IV1", Range("IV1").End(xlDown)).Clear
    End Sub
    					
  6. On the Excel menu in Microsoft Excel X for Mac and in later versions of Excel for Mac, or on the File menu in all other versions of Excel, click Close and Return to Microsoft Excel.
  7. Select A1:C6.
  8. On the Tools menu, point to Macro, and then click Macros. Select the SortAllRangeData macro, and then click Run.
All the data within the selected range is now sorted, and appears as follows:
   A1: 1    B1: 7    C1: 13
   A2: 2    B2: 8    C2: 14
   A3: 3    B3: 9    C3: 15
   A4: 4    B4: 10   C4: 16
   A5: 5    B5: 11   C5: 17
   A6: 6    B6: 12   C6: 18
				

REFERENCES

Excel X for Mac and later versions

For more information about how to sort, click Excel Help on the Help menu, type sorting, click Search, and then click a topic to view it.

For more information about how to use the Sort method from the Visual Basic Editor, click Visual Basic Help on the Help menu, type sort method, click Search, and then click to view sort method.

Excel 2001 for Mac

For more information about how to sort, click the Office Assistant, type sort a list, click Search, and then click a topic to view it.

Note If the Assistant is hidden, click the Office Assistant button on the Standard toolbar.

For more information about how to use the Sort method from the Visual Basic Editor, click the Office Assistant, type sort method, click Search, and then click to view sort method.

Properties

Article ID: 247311 - Last Review: September 18, 2011 - Revision: 7.0
APPLIES TO
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 2004 for Mac
  • Microsoft Excel X for Mac
  • Microsoft Excel 2001 for Mac
  • Microsoft Excel 98 for Macintosh
Keywords: 
dftsdahomeportal kbautomation kbprogramming kbhowto KB247311

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com