How do I convert an Excel data table into database format?

[riluma]

in a simple example I want to convert a table like:

SKU DESC Jan-12 Feb-12 Mar-12
123 prod1 10 42 34
345 prod2 90 10 39


Into database format like below, so that I can do data analysis with pivot table.

SKU DESC MONTH QTY
123 prod1 Jan-12 10
345 prod2 Jan-12 90
123 prod1 Feb-12 42
345 prod2 Feb-12 10
123 prod1 Mar-12 34
345 prod2 Mar-12 39

I know I can manually copy and paste the data [as links, so it will auto update if data changes in source tables], but its just too manual.

You can notice that the first 2 columns are preserved in the database design, but the month row was transformed in column and the values [quantities] were also transformed in column.

Is there any automated way to do this physical format conversion?
Maybe through a macro template that can be customised?

 

[Sal Paradise]

You can use a pivot table to do it.

Arrange your data like this:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">SKU</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">DESC</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">データ</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">集計</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">123</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">prod1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">合計 / 12-Mar</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">34</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">合計 / 12-Feb</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">42</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">合計 / 12-Jan</td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">123 合計 / 12-Mar</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">34</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">123 合計 / 12-Feb</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">42</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">123 合計 / 12-Jan</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">345</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">prod2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">合計 / 12-Mar</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">39</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">合計 / 12-Feb</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">合計 / 12-Jan</td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">90</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">345 合計 / 12-Mar</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">39</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">345 合計 / 12-Feb</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">345 合計 / 12-Jan</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">90</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">全体の 合計 / 12-Mar</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">73</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">全体の 合計 / 12-Feb</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">52</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">全体の 合計 / 12-Jan</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">100</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet4</p><br /><br />

Just remove the totals parts (sorry about the Japanese excel).

SKU and DESC go in the left side, every month goes in the "Data" section.

 

[tweedle]

this would be my stab at it...

Option Explicit

Type arec
    SKU As String
    DESCRIPTION As String
    MONTH As String
    QTY As Integer
End Type

Sub builddata()
    Application.ScreenUpdating = False
    
    Dim ws, wsTgt As Worksheet
    Dim lsc, lsr, tgtrow, arrsz, arridx, i, j As Long
    Dim recs() As arec

    Set ws = ActiveSheet

    If Not IsDate(ws.Cells(1, 3)) Then
        MsgBox "This doesn't look like the expected data." & vbLf & "Exiting process"
        Exit Sub
    End If
    lsc = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    lsr = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    arrsz = (lsc - 2) * (lsr - 1) - 1

    ReDim recs(arrsz) As arec
    arridx = 0

    For i = 2 To lsr
        For j = 3 To lsc
            recs(arridx).SKU = Cells(i, 1)
            recs(arridx).DESCRIPTION = Cells(i, 2)
            recs(arridx).MONTH = "'" & Format(Cells(1, j), "MMM-YY")
            recs(arridx).QTY = Cells(i, j)
            arridx = arridx + 1
        Next j
    Next i

    Set wsTgt = Worksheets.Add(After:=Sheets(Sheets.Count))

    wsTgt.Cells(1, 1) = "SKU"
    wsTgt.Cells(1, 2) = "DESC"
    wsTgt.Cells(1, 3) = "MONTH"
    wsTgt.Cells(1, 4) = "QTY"

    tgtrow = 2
    For i = LBound(recs()) To UBound(recs())
        wsTgt.Cells(tgtrow, 1) = recs(i).SKU
        wsTgt.Cells(tgtrow, 2) = recs(i).DESCRIPTION
        wsTgt.Cells(tgtrow, 3) = recs(i).MONTH
        wsTgt.Cells(tgtrow, 4) = recs(i).QTY

        tgtrow = tgtrow + 1
    Next i

    wsTgt.Name = "db " & CStr(wsTgt.Index)
    
    ws.Activate
    Application.ScreenUpdating = True

End Sub

 

[riluma]

Arigatou gozaimasu.

could you please attach your pivot table to your next reply?
I couldn't reproduce your design.

What I need is to convert the table design into database design but using links to the table design [but I'm still interested in seeing your pivot table]. since the data entry is made in table design, I need the linked Excel database to immediately update its data when the source tables are updated, without any manual refresh that would be required by a pivot table.

The logistic is that each office will send me their data table every month.
Then each file has a table with product info on the left columns and the columns on the right are the months, populated with quantities, so that it's 12 columns with quantities.

So I have a big Excel [Excel linked database] file where I pasted-as-link to each Excel data table file that I will receive so that I have the database ready for the entire year [the file names will always be the same every month for each office and saved in the same folder every month. so each month will have an additional month column populated with data. In the beginning of the year, my linked Excel will show the product names, but quantities will be 0, then every month I just open the source Excel data tables and my linked Excel database will automatically update and I can refresh the pivot tables with consolidated data].