-3

I have a list in Excel 2003 with employer information.

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

here

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:

here

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?.

Community
  • 1
  • 1
fightsaber
  • 31
  • 1
  • 6
  • What have you tried until now? Can you post the data but not as image? If you want somebody to try to reply your data, it's really annoying to be typing all this data. – Foxfire And Burns And Burns Mar 08 '18 at 11:48
  • Open the XLS in Excel 2016, use Power Query then save the results as an XLS. –  Mar 08 '18 at 11:49
  • [idownvotedbecau.se/noattempt](http://idownvotedbecau.se/noattempt/) and [idownvotedbecau.se/nomcve](http://idownvotedbecau.se/nomcve/) –  Mar 08 '18 at 11:54

1 Answers1

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
Chronocidal
  • 6,827
  • 1
  • 12
  • 26