Split information from 1 column to 4 columns in Excel 2003

Question

I have a list in Excel 2003 with employer information.

I receive the list with the information in the same column:

something dates:

    SECTOR X
NAME
KETCHUP, ASH
25/01/2017
31/02/2017
DORKMAN, RYAN
28/01/2017
30/05/2017
PEROTI, MAMA
26/01/2017
13/02/2017
28/06/2017
 SECTOR Y 
NAME
JIMENEZ, PEPE
16/01/2017
REDFIED, CHRIS
12/01/2017
JUMILLA , MANUEL
02/01/2017
12/01/2017
22/07/2017
30/07/2017
 SECTOR U 
NAME
KENEDY, LION
16/04/2017
VALENTINE, JILL
12/07/2017
KEPER, KNOR
02/03/2017
12/03/2017
22/10/2017
30/10/2017

I have more than 100 names each with their respective months.

I paste like this:

something dates

SECTORS       NAME             DATE    TOTAL
SECTOR X      KETCHUP, ASH     jan/17    1
                               feb/17    1
              DORKMAN, RYAN    jan/17    1
                               may/17    1
              PEROTI, MAMA     jan/17    1
                               feb/17    1
                               jun/17    1
SECTOR Y            
             JIMENEZ, PEPE     jan/17    1
             REDFIED, CHRIS    jan/17    1
             JUMILLA , MANUEl  jan/17    2
                               juL/17    2
 SECTOR U           
             KENEDY, LION      apr/17    1
             VALENTINE, JILL   jan/17    1
             KEPER, KNOR       mar/17    2
                               oct/17    2

I'm trying to do a dynamic table but I don't know how separate the information into 4 columns. How can I convert the "sector name" into a column?.

Answer 1

I would recommend a macro to reformat this into a pivotable format first, and then apply a PivotTable to get your desired output.

You want this macro to run down the column, and apply the first of the following rules that is true for each cell:

1. If the next value is "Name", then store the current value as a Sector Name
2. If the current value is "Name", then do nothing
3. If the current value is not a date then store it as a Person Name
4. If the current value is a Date, then add a new row to our table with the Sector Name, Person Name and current value as Date

This will put a row for each date, with the Sector and Person Name in the same row, which you can then turn into a PivotTable:

Sector | Name | Date
Sector X| Ketchup, Ash | 25/01/2017
Sector X| Ketchup, Ash | 31/02/2017

Sub MakePivotable(ByRef SourceColumn AS Range, ByRef Output As Range)
    Dim WorkCell As Range, OutRow As Range
    Dim Sector As String, Person As String
    Set Output = Output.Cells(1,1) 'We only want the top-left cell here
    Set SourceColumn = Intersect(SourceColumn, SourceColumn.Worksheet.UsedRange) 'Ignore unused rows

    Output.Value = "Sectors"
    Output.Offset(0,1).Value = "Name"
    Output.Offset(0,2)Value = "Date"
    OutRow = 1
    Sector = ""
    Person = ""
    For Each WorkCell In SourceColumn.Cells
        IF WorkCell.Offset(1,0).Value = "Name" Then 'Is Sector
            Sector = WorkCell.Value
        ElseIf WorkCell.Value = "Name" Then 'Do Nothing

        ElseIf Not IsDate(WorkCell.Value) Then ' Is Name
            Person = WorkCell.Value
        ElseIf 'Is Date
            'Create the row
            Output.Offset(OutRow,0).Value = Sector
            Output.Offset(OutRow,0).Value = Name
            Output.Offset(OutRow,0).Value = cDate(WorkCell.Value)
            OutRow = OutRow+1 'Move on to the next row
        End If
    Next WorkCell
End Sub