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 !

;)
 

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?
 

Forum statistics

Threads
1,085,714
Messages
5,385,415
Members
401,944
Latest member
Hellodoublev

Some videos you may like

This Week's Hot Topics

Top