Transposing data

Microsoft Ms sla excel lic/sa 3yr-1
May 2, 2010 at 17:29:24
Specs: Windows XP
have a Excel file with 2 cols - Mothers Name and Childrens Date of Birth. File contains about 20k records and Mothers Name is repeated for as many children she has (i.e., Mary, 03/09/1992, Mary 05/21/1996, Mary, 12/13/2002). My goal is to have an Excel spreadsheet with cols as Mothers Name, DOB1, DOB2, DOB3, DOB4, DOB5)

See More: Transposing data

May 3, 2010 at 08:23:08

This macro creates a list of unique mothers names on a worksheet named "Sorted" and adds all birth dates into successive columns for each mother.
The source data is in columns A and B on a worksheet named "Source"

The following includes instructions for adding a button to the Source worksheet in Excel 2003. If you have Excel 2007, several other recent posts include instructions for Excel 2007.

On the Worksheet named "Source", create a command button from the Control Toolbox toolbar.
(If this isn't visible, right click on an existing toolbar and check the Control Toolbox).
Select the button Icon and draw a button
Right-click the button and select Command Button - Edit and change the name to 'Sort' or something else meaningful.
Right-click the button again and select View Code
In the code window that opens enter this:

Option Explicit

Private Sub CommandButton1_Click()
Dim rngSrcStart As Range
Dim rngSrcEnd As Range
Dim rngDestStart As Range
Dim rngDestEnd As Range
Dim rngCellSrc As Range
Dim rngCellDest As Range
Dim blnNotFound As Boolean
Dim blnMoved As Boolean

On Error GoTo ErrHnd

'set start of source data
Set rngSrcStart = Worksheets("Source").Range("A2")
'find end of source data
Set rngSrcEnd = Worksheets("Source").Range("A" & CStr(Application.Rows.Count)).End(xlUp)
'set start and end of destination data as same number of rows to start with
Set rngDestStart = Worksheets("Sorted").Range("A" & rngSrcStart.Row)
Set rngDestEnd = Worksheets("Sorted").Range("A" & rngSrcEnd.Row)

'copy unique names to destination worksheet ("Sorted")
For Each rngCellSrc In Worksheets("Source").Range(rngSrcStart, rngSrcEnd)
    'get name and compare to existing names in destination
    blnNotFound = True
    For Each rngCellDest In Worksheets("Sorted").Range(rngDestStart, rngDestEnd)
        If rngCellSrc.Text = rngCellDest.Text Then
            blnNotFound = False
        End If
        'name already present - no need to keep looking
        If blnNotFound = False Then Exit For
    Next rngCellDest
    'if name not found - add it
    If blnNotFound = True Then
        Worksheets("Sorted").Range(rngDestEnd.Address).End(xlUp).Offset(1, 0).Value _
                    = rngCellSrc.Text
    End If
Next rngCellSrc

'reset end of destination range
rngDestEnd = Worksheets("Sorted").Range(rngDestEnd.Address).End(xlUp)

'now copy dates to appropriate names on destination sheet
For Each rngCellSrc In Worksheets("Source").Range(rngSrcStart, rngSrcEnd)
    blnMoved = False
    For Each rngCellDest In Worksheets("Sorted").Range(rngDestStart, rngDestEnd)
        If rngCellSrc.Text = rngCellDest.Text Then
            'name found - add date in next empty column
            Worksheets("Sorted").Cells(rngCellDest.Row, Application.Columns.Count) _
                    .End(xlToLeft).Offset(0, 1).Value _
                    = rngCellSrc.Offset(0, 1).Text
                    blnMoved = True
        End If
        'moved - so no need to continue looking for match
        If blnMoved = True Then Exit For
    Next rngCellDest
Next rngCellSrc

Exit Sub

'error handler
End Sub

Note that Private Sub CommandButton1_Click() and End sub will already be present, so don't duplicate them. Option Explicit goes before Private Sub CommandButton1_Click().
Some lines of code have been split onto two lines for ease of viewing, using the line continuation character "_". This should work 'as is' just copy and paste, or you could remove the "_" and bring the code back to one line.

Click Save from the Visual Basic Menu.

Alt+f11 takes you back to the main Excel window.
Exit design mode (first icon on the Controls Toolbox toolbar).

As changes made by Macros cannot be undone with the Undo button, test this macro on a copy of your data. Always make a backup of your Workbook before running this macro. This code has only been tested on sample data, and it has not been tested in your environment, so test it on copies of your data to ensure that it works 'as expected'

Click the 'Sort' button to run the macro.


Report •

May 3, 2010 at 12:18:17
With just a first name, how do you plan to separate out different generations?

My mother was Mary Margaret,
my grandmother was Mary Francis.
My aunt is also named Mary

Which Mary goes with which dates?

Don't you need a bit more info to correctly sort the first name with the child date of birth?


Report •
Related Solutions

Ask Question