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

[Feyroce]

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	SG11	Jan-12	1213
xyz-1	PS-A	F1	SG1	SG11	Feb-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	SG32	Feb-12	2543
xyz-2	PS-B	F1	SG3	SG32	Mar-12	4785
xyz-2	PS-B	F1	SG3	SG32	...	...

<tbody>
</tbody>

Thanks in advance for your good suggestions !

 

[nise23]

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]

Hi,
I have a solution with vba:
An excel with two sheets:
-one with initial table ( I named it "data")

[RANGE=cls:xl2bb-100][XR][XH=cs:10]Excel 2007[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][XH]C[/XH][XH]D[/XH][XH]E[/XH][XH]F[/XH][XH]G[/XH][XH]H[/XH][XH]I[/XH][/XR][XR][XH]1[/XH][XD=h:l|bc:fafafa|c:333333]Material-ID[/XD][XD=h:l|bc:fafafa|c:333333]Product segment[/XD][XD=h:l|bc:fafafa|c:333333]Factory[/XD][XD=h:l|bc:fafafa|c:333333]Sub-group 1[/XD][XD=h:l|bc:fafafa|c:333333]Sub-group 2[/XD][XD=h:l|bc:fafafa|c:333333]Jan[/XD][XD=h:l|bc:fafafa|c:333333]Feb[/XD][XD=h:l|bc:fafafa|c:333333]Mar[/XD][XD=h:l|bc:fafafa|c:333333]Apr[/XD][/XR][XR][XH]2[/XH][XD=h:l|bc:fafafa|c:333333]xyz-1[/XD][XD=h:l|bc:fafafa|c:333333]PS-A[/XD][XD=h:l|bc:fafafa|c:333333]F1[/XD][XD=h:l|bc:fafafa|c:333333]SG1[/XD][XD=h:l|bc:fafafa|c:333333]SG11[/XD][XD=h:r|bc:fafafa|c:333333]1213[/XD][XD=h:r|bc:fafafa|c:333333]1312[/XD][XD=h:r|bc:fafafa|c:333333]2450[/XD][XD=h:r|bc:fafafa|c:333333]1223[/XD][/XR][XR][XH]3[/XH][XD=h:l|bc:fafafa|c:333333]xyz-2[/XD][XD=h:l|bc:fafafa|c:333333]PS-B[/XD][XD=h:l|bc:fafafa|c:333333]F1[/XD][XD=h:l|bc:fafafa|c:333333]SG3[/XD][XD=h:l|bc:fafafa|c:333333]SG32[/XD][XD=h:r|bc:fafafa|c:333333]2415[/XD][XD=h:r|bc:fafafa|c:333333]2543[/XD][XD=h:r|bc:fafafa|c:333333]4785[/XD][XD=h:r|bc:fafafa|c:333333]2315[/XD][/XR][XR][XH=cs:10][RANGE][XR][XD]data[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

-one with the final list (I named it "list")

[RANGE=cls:xl2bb-100][XR][XH=cs:8]Excel 2007[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][XH]C[/XH][XH]D[/XH][XH]E[/XH][XH]F[/XH][XH]G[/XH][/XR][XR][XH]1[/XH][XD=h:l|bc:fafafa|c:333333]Material-ID[/XD][XD=h:l|bc:fafafa|c:333333]Product segment[/XD][XD=h:l|bc:fafafa|c:333333]Factory[/XD][XD=h:l|bc:fafafa|c:333333]Sub-group 1[/XD][XD=h:l|bc:fafafa|c:333333]Sub-group 2[/XD][XD=h:l|bc:fafafa|c:333333]Month[/XD][XD=h:l|bc:fafafa|c:333333]Value[/XD][/XR][XR][XH]2[/XH][XD=h:l]xyz-1[/XD][XD=h:l]PS-A[/XD][XD=h:l]F1[/XD][XD=h:l]SG1[/XD][XD=h:l]SG11[/XD][XD=h:l]Jan[/XD][XD=h:r]1213[/XD][/XR][XR][XH]3[/XH][XD=h:l]xyz-1[/XD][XD=h:l]PS-A[/XD][XD=h:l]F1[/XD][XD=h:l]SG1[/XD][XD=h:l]SG11[/XD][XD=h:l]Feb[/XD][XD=h:r]1312[/XD][/XR][XR][XH]4[/XH][XD=h:l]xyz-1[/XD][XD=h:l]PS-A[/XD][XD=h:l]F1[/XD][XD=h:l]SG1[/XD][XD=h:l]SG11[/XD][XD=h:l]Mar[/XD][XD=h:r]2450[/XD][/XR][XR][XH]5[/XH][XD=h:l]xyz-1[/XD][XD=h:l]PS-A[/XD][XD=h:l]F1[/XD][XD=h:l]SG1[/XD][XD=h:l]SG11[/XD][XD=h:l]Apr[/XD][XD=h:r]1223[/XD][/XR][XR][XH]6[/XH][XD=h:l]xyz-2[/XD][XD=h:l]PS-B[/XD][XD=h:l]F1[/XD][XD=h:l]SG3[/XD][XD=h:l]SG32[/XD][XD=h:l]Jan[/XD][XD=h:r]2415[/XD][/XR][XR][XH]7[/XH][XD=h:l]xyz-2[/XD][XD=h:l]PS-B[/XD][XD=h:l]F1[/XD][XD=h:l]SG3[/XD][XD=h:l]SG32[/XD][XD=h:l]Feb[/XD][XD=h:r]2543[/XD][/XR][XR][XH]8[/XH][XD=h:l]xyz-2[/XD][XD=h:l]PS-B[/XD][XD=h:l]F1[/XD][XD=h:l]SG3[/XD][XD=h:l]SG32[/XD][XD=h:l]Mar[/XD][XD=h:r]4785[/XD][/XR][XR][XH]9[/XH][XD=h:l]xyz-2[/XD][XD=h:l]PS-B[/XD][XD=h:l]F1[/XD][XD=h:l]SG3[/XD][XD=h:l]SG32[/XD][XD=h:l]Apr[/XD][XD=h:r]2315[/XD][/XR][XR][XH=cs:8][RANGE][XR][XD]List[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

and the code is:

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]

sorry,
I wanted to post the example tables.
how i do that using the html code made by MrExcelHtml?