Transform an Excel sales table with multiple monthly sales column into an Access DB

Feyroce

New Member
Joined
Jun 15, 2012
Messages
1
Getting sales volumes and quantities from a SAP-based system into an Excel sheet, I'm looking for an easy way (VBA or function) to transform those data into an Access DB.

The actual format of the data is looking like this:

Material-ID
Product segment
Factory
Sub-group 1
Sub-group 2
Jan-12
Feb-12
Mar-12
...
Dec-12
xyz-1
PS-A
F1
SG1
SG11
1213
1312
2450
1223
xyz-2
PS-B
F1
SG3
SG32
2415
2543
4785
2315

<tbody>
</tbody>
After transformation, the data should look like this:

Material-ID
Product segment
Factory
Sub-group 1
Sub-group 2
Date
Sales
xyz-1
PS-A
F1
SG1
SG11Jan-12
1213
xyz-1
PS-A
F1
SG1SG11Feb-12
1312
xyz-1
PS-A
F1
SG1
SG11
Mar-12
2450
xyz-1
PS-A
F1
SG1
SG11
...
...
xyz-1
PS-A
F1
SG1
SG11
Dec-12
1223
xyz-2
PS-B
F1
SG3
SG32
Jan-12
2415
xyz-2
PS-B
F1
SG3
SG32Feb-12
2543
xyz-2
PS-B
F1
SG3SG32
Mar-12
4785
xyz-2
PS-B
F1
SG3
SG32
...
...

<tbody>
</tbody>

Thanks in advance for your good suggestions !

;)
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

nise23

Board Regular
Joined
Jan 6, 2012
Messages
65
Interesting question! I would like to see some suggestions for this. I rely mostly on functions, so if there are not too many material ID's (unlikely I know) I would probably create the table by hand for columns A through F and use a HLOOKUP formula to look up the corresponding sales number. But that would be pretty tedious for a large number of items, so there must be a better way!

-nise23
 

wizz

New Member
Joined
May 13, 2010
Messages
17
Hi,
I have a solution with vba:
An excel with two sheets:
-one with initial table ( I named it "data")
HTML:
<b>Excel 2007</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 /><col /><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><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Material-ID</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Product segment</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Factory</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Sub-group 1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Sub-group 2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Jan</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Feb</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Mar</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Apr</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">xyz-1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">PS-A</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">F1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">SG1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">SG11</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;color: #333333;background-color: #FAFAFA;;">1213</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;color: #333333;background-color: #FAFAFA;;">1312</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;color: #333333;background-color: #FAFAFA;;">2450</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;color: #333333;background-color: #FAFAFA;;">1223</td></tr><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;color: #333333;background-color: #FAFAFA;;">xyz-2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">PS-B</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">F1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">SG3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">SG32</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;color: #333333;background-color: #FAFAFA;;">2415</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;color: #333333;background-color: #FAFAFA;;">2543</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;color: #333333;background-color: #FAFAFA;;">4785</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;color: #333333;background-color: #FAFAFA;;">2315</td></tr></tbody></table><p style="width:2.4em;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">data</p><br /><br />


-one with the final list (I named it "list")
HTML:
<b>Excel 2007</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 /><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><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Material-ID</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Product segment</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Factory</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Sub-group 1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Sub-group 2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Month</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Value</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-top: 1px solid black;;">xyz-1</td><td style="border-top: 1px solid black;;">PS-A</td><td style="border-top: 1px solid black;;">F1</td><td style="border-top: 1px solid black;;">SG1</td><td style="border-top: 1px solid black;;">SG11</td><td style="border-top: 1px solid black;;">Jan</td><td style="text-align: right;border-top: 1px solid black;;">1213</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">xyz-1</td><td style=";">PS-A</td><td style=";">F1</td><td style=";">SG1</td><td style=";">SG11</td><td style=";">Feb</td><td style="text-align: right;;">1312</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">xyz-1</td><td style=";">PS-A</td><td style=";">F1</td><td style=";">SG1</td><td style=";">SG11</td><td style=";">Mar</td><td style="text-align: right;;">2450</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">xyz-1</td><td style=";">PS-A</td><td style=";">F1</td><td style=";">SG1</td><td style=";">SG11</td><td style=";">Apr</td><td style="text-align: right;;">1223</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">xyz-2</td><td style=";">PS-B</td><td style=";">F1</td><td style=";">SG3</td><td style=";">SG32</td><td style=";">Jan</td><td style="text-align: right;;">2415</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">xyz-2</td><td style=";">PS-B</td><td style=";">F1</td><td style=";">SG3</td><td style=";">SG32</td><td style=";">Feb</td><td style="text-align: right;;">2543</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">xyz-2</td><td style=";">PS-B</td><td style=";">F1</td><td style=";">SG3</td><td style=";">SG32</td><td style=";">Mar</td><td style="text-align: right;;">4785</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">xyz-2</td><td style=";">PS-B</td><td style=";">F1</td><td style=";">SG3</td><td style=";">SG32</td><td style=";">Apr</td><td style="text-align: right;;">2315</td></tr></tbody></table><p style="width:2.4em;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">List</p><br /><br />


and the code is:


Code:
Public Sub transform()
Dim i, j As Integer
Dim LastRow, LastColumn As Integer
Dim CurrentRow As Integer


Sheets("data").Activate
LastRow = ActiveSheet.UsedRange.Rows.Count
LastColumn = ActiveSheet.UsedRange.Columns.Count
CurrentRow = 2
For i = 2 To LastRow
    For j = 6 To LastColumn
        Sheets("List").Range("A" & Trim(Str(CurrentRow)) & ":E" & Trim(Str(CurrentRow))).Value = _
            Sheets("data").Range("A" & Trim(Str(i)) & ":E" & Trim(Str(i))).Value
        'I insert the month:
        Sheets("List").Cells(CurrentRow, "F").Value = Sheets("data").Cells(1, j).Value
        'I insert the value of the month:
        Sheets("List").Cells(CurrentRow, "G").Value = Sheets("data").Cells(i, j).Value
        CurrentRow = CurrentRow + 1
    Next j
Next i
End Sub
 

wizz

New Member
Joined
May 13, 2010
Messages
17
sorry,
I wanted to post the example tables.
how i do that using the html code made by MrExcelHtml?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,309
Messages
5,641,448
Members
417,209
Latest member
Agbarker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top