Normalizing an Excel Table

Question

I have excel table exported from another system. I need to upload this into a database and am looking for a way to normalize it.

Current structure:

customerid   date1   date2   date3 ... date85
1             1/1     1/4     2/4       
2             3/1
3             4/1     4/12

Need to convert to:

customerid     date
1               1/1
1               1/4
1               2/4
2               3/1
3               4/1
3               4/12

I'm on a Mac and have excel 2016. I don't have any addons like power pivot.

Answer 1

Here's what I came up with, I hope VBA isn't that much different on Mac then it is on PC, otherwise this may not work.

The code is fairly commented, although feel free to ask if there are questions.

'Helper function to find the last Column
Public Function getLastColumn(strSheet, strColum) As Integer
    Dim rng As Range
    Set rng = Sheets(strSheet).Cells.Find(What:="*", _
            After:=Sheets(strSheet).Range("A1"), _
            Lookat:=xlPart, _
            LookIn:=xlFormulas, _
            SearchOrder:=xlByColumns, _
            SearchDirection:=xlPrevious, _
            MatchCase:=False)

    If rng Is Nothing Then
        getLastColumn = 1
    Else
        getLastColumn = rng.Column
    End If
End Function

'Helper function to find the lastRow
Public Function getLastRow(strSheet, strColum) As Long
    Dim rng As Range: Set rng = Worksheets(strSheet).Range(strColum & "1")
    getLastRow = Worksheets(strSheet).Cells(Rows.Count, rng.Column).End(xlUp).row
End Function

Public Sub Normalize_Table()
    Dim LastRow As Long: LastRow = getLastRow("Sheet1", "A") ' First Parameter is the Sheet Name,
                                                             ' Second is the column you want to count
    Dim LastColumn As Integer: LastColumn = getLastColumn("Sheet1", "A")
    Dim RowCounter As Long: RowCounter = 1 ' Starting Row
    Dim RowID As Variant ' RowID, basically this is the repeated Column 1 value
    Dim row As Object
    Dim col As Object
    Dim rng As Range: Set rng = Sheets("Sheet1").Range(Sheets("Sheet1").Cells(1, 1), _
                                Sheets("Sheet1").Cells(LastRow, LastColumn)) ' Get the range you want to Normalize to another sheet/range

    'Iterate the range, go through each row, and each column
    'Making a new row for each column value, only update the value
    'of the first column when a start a new row
    For Each row In rng.Rows
        RowID = rng.Cells(row.row, 1)
        For Each col In rng.Columns
            'Assuming you want to add this to a new sheet, Let's say "Sheet2"
            Sheets("Sheet2").Cells(RowCounter, 1) = RowID
            If col.Column > 1 Then
                Sheets("Sheet2").Cells(RowCounter, 2) = rng(row.row, col.Column)
                RowCounter = RowCounter + 1
            End If
        Next
    Next       
End Sub

Answer 2

You can use the Pivot Table Wizard for this (which is not an add-on, it's built-in Excel functionality):

1. Press Alt, D, P to open the Pivot Table Wizard
2. Select "Multiple Consolodation Ranges", and click Next
3. Select "I will create the page fields", and click Next
4. With the cursor in the "Range" textbox, select your range of data, including row headers (in your example, I believe that would be A1:CH4), and click Add, then click Next
5. Select "New worksheet", and click Finish
6. In the new worksheet that is generated, double-click in the bottom-right cell (the "Grand Total" cell). This will open a second new worksheet containing your normalized data.
7. To remove blank values in the second new worksheet, click the filter icon in the "Value" column, and de-select the "(Blanks)" checkbox
8. Copy the resulting data to the clipboard, and paste wherever it is needed.

Answer 3

Here is a VBA method that should work quite rapidly, even on a large data base. Note that you have to rename the class module as noted in that module.

Also note that you may have to rename wsSrc and wsRes -- the worksheets with your source data and where you want the results to go.

There is also an area near the end of the regular module where I do some rudimentary formatting. You can certainly adjust that to pretty things up, if you need to.

Class Module

---

Option Explicit

'Rename cCustDTS

Private pID As String
Private pDT As Date
Private pDTs As Collection

Private Sub Class_Initialize()
    Set pDTs = New Collection
End Sub

Public Property Get ID() As String
    ID = pID
End Property
Public Property Let ID(Value As String)
    pID = Value
End Property

Public Property Get DT() As Date
    DT = pDT
End Property
Public Property Let DT(Value As Date)
    pDT = Value
End Property

Public Property Get DTs() As Collection
    Set DTs = pDTs
End Property
Public Function ADDdt(Value As Date)
    pDTs.Add Value
End Function

---

Regular Module

---

Option Explicit
Sub NormalizeDates()
    Dim vSrc As Variant, vRes As Variant
    Dim wsSrc As Worksheet, wsRes As Worksheet, rRes As Range
    Dim cCD As cCustDTS, colCD As Collection
    Dim I As Long, J As Long, LineCount As Long
    Dim LastRow As Long, LastCol As Long
    Dim V As Variant, W As Variant

'Set Source and Results Worksheets and Ranges
Set wsSrc = Worksheets("sheet1")
Set wsRes = Worksheets("sheet2")
    Set rRes = wsRes.Cells(1, 1)

'Get Source Data
With wsSrc
    LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    vSrc = .Range(.Cells(1, 1), .Cells(LastRow, LastCol))
End With

'Collect and organize the data
Set colCD = New Collection
For I = 2 To UBound(vSrc, 1) 'Skip the first row
    Set cCD = New cCustDTS
    With cCD
        .ID = vSrc(I, 1)
        For J = 2 To UBound(vSrc, 2)
            If IsDate(vSrc(I, J)) Then
                .DT = vSrc(I, J)
                .ADDdt .DT
            End If
        Next J
        colCD.Add cCD
        LineCount = LineCount + .DTs.Count
    End With
Next I

'Organize the data for output
ReDim vRes(0 To LineCount, 1 To 2)
    vRes(0, 1) = "Customer ID"
    vRes(0, 2) = "Date"
    I = 0
For Each V In colCD
    For Each W In V.DTs
        I = I + 1
        vRes(I, 1) = V.ID
        vRes(I, 2) = W
    Next W
Next V

'Write to the output sheet and format
Set rRes = rRes.Resize(rowsize:=UBound(vRes, 1) + 1, columnsize:=UBound(vRes, 2))
With rRes
    .EntireColumn.Clear
    .Value = vRes
    With .Rows(1)
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
    End With
    With .Columns(2)
        .NumberFormat = "m/d"
    End With
    .EntireColumn.AutoFit
    .Columns(2).ColumnWidth = .Columns(2).ColumnWidth * 2
End With

End Sub

---

Answer 4

Assuming customerid is in A1, please try (I haven't!) in Row 2 in a column:

=OFFSET(A$2,INT((ROW()-2)/85),)  

and in another column:

 =OFFSET(B$2,INT(ROW()/85),MOD(ROW()-2,85),)  

Copy down to suit, select the formulae cells, Copy, Paste Special..., Values over the top and then Filter to remove zeros in 'another' column.

Beware if doing this all in the one sheet as deleting rows might also delete some of your source data.

Add labels.