Large volume data sort

Microsoft Excel 2003 (full product)
January 12, 2010 at 04:45:42
Specs: Windows XP
I have a spreadsheet with a large number of row that have corresponding columns that need to be sorted and then moved to a new cheet. For example, in each row is an employee name and in the corresponding 16 columns, I have benefit codes (the same code and be in column 1 row 1 and in column 13 for row 2.) How do I sort all of the like codes in the columns into 1sheet?

employee, column 1, column 2, ....column 16
John Doe, 10, 20, ...15
Jane Smith,20, 35, ...12
B Harris, 15, 35, ...10

Any thoughts?

See More: Large volume data sort

Report •

January 12, 2010 at 06:43:17
To sort rows:

Select your columns & rows you want to sort,
On the Task Bar,
Select Data,
Select Sort,
In the Sort Window, Select Options
At the bottom under the heading Orientation
Select Sort Left to Right
Click Ok
Click OK

It may not get all the rows of numbers lined up exactly, but it should get you fairly close, so that a bit of copy/paste should not be difficult.

With more info there is probably a VBA solution that could place each code into a specified column, but that is a bit beyond my abilities.


Report •

January 15, 2010 at 07:46:09
What I am looking for is code for a macro that automatically sort each column and group them together so I don't have to do a manual sort each time.

Report •

January 15, 2010 at 10:33:17

Assuming that the source data with names and 16 columns is on a sheet named SrcData (you will need to name the source data worksheet),
and that row 1 contains headings, with data starting on Row 2,
and no data in cells in column A below the last name.

The sorted data will go into a worksheet named SortedData. You will need to create and name this worksheet.

The data is sorted by code with the names of each person who has that code listed.
I am not sure if this is exactly what you intended - please let me know - preferably with an example showing what the output should look like.
(When posting spreadsheet cell data please enclose it in <pre> </pre> tags - there is an icon for this above the reply box - this retains data in columns - more or less!).

Try this code.
I suggest creating a command button so that this can be re-run as required.
Worksheet command button:
On the sheet with the source data create a command button from the Control Toolbox toolbar.
(If this toolbar isn't visible, right click on an existing toolbar and check the Control Toolbox from the list that

Select the button Icon and draw a button.
Right-click the button and select Command Button - Edit and change the name to something usefull!
Right-click the button again and select View Code.
In the visual basic code window that opens enter the code.
Note that Private Sub CommandButton1_Click() and End Sub will already be there, so don't duplicate them.
Option Explicit goes before the line Private Sub CommandButton1_Click().

Option Explicit

Private Sub CommandButton1_Click()
Dim strSrcName As String
Dim strDestName As String
Dim rngFirstData As Range
Dim rngLastData As Range
Dim objFind As Object
Dim rngCell As Range
Dim intCodeMin As Integer
Dim intCodeMax As Integer
Dim intDestRow As Integer
Dim intDestCol As Integer
Dim n As Integer

On Error GoTo ErrHnd

'Source & Destination worksheet names
strSrcName = "SrcData"
strDestName = "SortedData"

With ActiveWorkbook
    'setup first and last cells
    Set rngFirstData = .Worksheets(strSrcName).Range("A1")
    Set rngLastData = .Worksheets(strSrcName).Range("A65534").End(xlUp)
    'get min and max code numbers in use
    intCodeMin = Application.WorksheetFunction. _
        Min(Range(rngFirstData.Offset(0, 1), rngLastData.Offset(0, 16)))
    intCodeMax = Application.WorksheetFunction. _
        Max(Range(rngFirstData.Offset(0, 1), rngLastData.Offset(0, 16)))
    'set destination column counter
    intDestCol = -1

    'find each code number
    For n = intCodeMin To intCodeMax
        'test if code number used
        Set objFind = .Worksheets(strSrcName).Range(rngFirstData.Offset(0, 1), _
            rngLastData.Offset(0, 16)).Find(n, LookIn:=xlValues, LookAt:=xlWhole)
        If Not objFind Is Nothing Then
            'code number is used - create a heading
            'reset row counter and move to next column
            intDestRow = 0
            intDestCol = intDestCol + 1
            .Worksheets(strDestName).Range("A1").Offset(intDestRow, intDestCol).Value _
                = "Code Number " & Format(n, "00#")
            'reset row counter and move to next column
            intDestRow = intDestRow + 1
             'loop through the data
            For Each rngCell In Range(rngFirstData.Offset(0, 1), rngLastData.Offset(0, 16))
                'test for a matching code
                If rngCell.Value = n Then
                    'copy name
                    .Worksheets(strSrcName).Cells(rngCell.Row, 1).Copy _
                        Destination:=.Worksheets(strDestName).Range("A1"). _
                        Offset(intDestRow, intDestCol)
                    'increment destination row counter
                    intDestRow = intDestRow + 1
                End If
            Next rngCell
        End If
    Next n
    'resize columns
End With
Exit Sub
'error handler
End Sub

Note that some lines of code are split onto two lines for ease of viewing, using the line continuation character '_'.
The code should work when copied and pasted with the split lines
After entering the code click 'Save' from the Visual Basic Menu.
Use Alt+f11 (the Alt key and function key #11 together), to swap back to the main Excel window.
Exit Design mode - click the design icon on the Control Toolbox toolbar,
then click the Command button to run the code.


Report •

Related Solutions

Ask Question