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

F

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:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Material-ID
[/TD]
[TD]Product segment
[/TD]
[TD]Factory
[/TD]
[TD]Sub-group 1
[/TD]
[TD]Sub-group 2
[/TD]
[TD]Jan-12
[/TD]
[TD]Feb-12
[/TD]
[TD]Mar-12
[/TD]
[TD]...
[/TD]
[TD]Dec-12
[/TD]
[/TR]
[TR]
[TD]xyz-1
[/TD]
[TD]PS-A
[/TD]
[TD]F1
[/TD]
[TD]SG1
[/TD]
[TD]SG11
[/TD]
[TD]1213
[/TD]
[TD]1312
[/TD]
[TD]2450
[/TD]
[TD][/TD]
[TD]1223
[/TD]
[/TR]
[TR]
[TD]xyz-2
[/TD]
[TD]PS-B
[/TD]
[TD]F1
[/TD]
[TD]SG3
[/TD]
[TD]SG32
[/TD]
[TD]2415
[/TD]
[TD]2543
[/TD]
[TD]4785
[/TD]
[TD][/TD]
[TD]2315
[/TD]
[/TR]
</tbody>[/TABLE]
After transformation, the data should look like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Material-ID
[/TD]
[TD]Product segment
[/TD]
[TD]Factory
[/TD]
[TD]Sub-group 1
[/TD]
[TD]Sub-group 2
[/TD]
[TD]Date
[/TD]
[TD]Sales
[/TD]
[/TR]
[TR]
[TD]xyz-1
[/TD]
[TD]PS-A
[/TD]
[TD]F1
[/TD]
[TD]SG1
[/TD]
[TD]SG11[/TD]
[TD]Jan-12
[/TD]
[TD]1213
[/TD]
[/TR]
[TR]
[TD]xyz-1
[/TD]
[TD]PS-A
[/TD]
[TD]F1
[/TD]
[TD]SG1[/TD]
[TD]SG11[/TD]
[TD]Feb-12
[/TD]
[TD]1312
[/TD]
[/TR]
[TR]
[TD]xyz-1
[/TD]
[TD]PS-A
[/TD]
[TD]F1
[/TD]
[TD]SG1
[/TD]
[TD]SG11
[/TD]
[TD]Mar-12
[/TD]
[TD]2450
[/TD]
[/TR]
[TR]
[TD]xyz-1
[/TD]
[TD]PS-A
[/TD]
[TD]F1
[/TD]
[TD]SG1
[/TD]
[TD]SG11
[/TD]
[TD]...
[/TD]
[TD]...
[/TD]
[/TR]
[TR]
[TD]xyz-1
[/TD]
[TD]PS-A
[/TD]
[TD]F1
[/TD]
[TD]SG1
[/TD]
[TD]SG11
[/TD]
[TD]Dec-12
[/TD]
[TD]1223
[/TD]
[/TR]
[TR]
[TD]xyz-2
[/TD]
[TD]PS-B
[/TD]
[TD]F1
[/TD]
[TD]SG3
[/TD]
[TD]SG32
[/TD]
[TD]Jan-12
[/TD]
[TD]2415
[/TD]
[/TR]
[TR]
[TD]xyz-2
[/TD]
[TD]PS-B
[/TD]
[TD]F1
[/TD]
[TD]SG3
[/TD]
[TD]SG32[/TD]
[TD]Feb-12
[/TD]
[TD]2543
[/TD]
[/TR]
[TR]
[TD]xyz-2
[/TD]
[TD]PS-B
[/TD]
[TD]F1
[/TD]
[TD]SG3[/TD]
[TD]SG32
[/TD]
[TD]Mar-12
[/TD]
[TD]4785
[/TD]
[/TR]
[TR]
[TD]xyz-2
[/TD]
[TD]PS-B
[/TD]
[TD]F1
[/TD]
[TD]SG3
[/TD]
[TD]SG32
[/TD]
[TD]...
[/TD]
[TD]...
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance for your good suggestions !

;-)
 
Sort by date Sort by votes
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
 
Upvote 0
Hi,
I have a solution with vba:
An excel with two sheets:
-one with initial table ( I named it "data")
HTML: 
[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")
HTML: 
[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:


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
 
Upvote 0
sorry,
I wanted to post the example tables.
how i do that using the html code made by MrExcelHtml?
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Managing Payment table separate from billing, calculating lps on every received payment based on criteria.
Replies
1
Views
684
NaushadAnwar
N
B
  • Question Question
Calculating sales number brackets in a table
Replies
4
Views
814
DanteAmor
DanteAmor
Y
  • Question Question
Calculate repeat sales
Replies
1
Views
607
James006
James006
J
  • Question Question
Excel macro for Remove comma's and spaces from a cell and place the data in different cell
Replies
3
Views
1K
JEC
JEC
J
  • Question Question
Creating a Table
Replies
0
Views
467
jwgreen1986
J

Share this page

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
Back
Top