Changing the layout of an Excel 2010 SAP report

A

Anita Kay

New Member
Joined
Jan 26, 2017
Messages
8
Hello all,

I have been coming the archives of many forums trying to find a good, effective and safe solution for changing the layout of an SAP report exported to Excel 2010. It takes months and months to get them to provide us with information and while we have asked for raw data they seem hesitant to give it to us. They always want to provide us with a report. I am not proficient with VBA but can get around. I do not understand a lot about looping.

What I need to do it to take the spreadsheet and insert twelve rows after each existing record, copy the information from columns A:L into the blanks beneath each populated row. I do that often with GoTo, Special, Find Blanks and a simple formula like =A2. The real problem lies with M:X. The reports is given with twelve months of data laid out in each column. Row 1 is the Date, row 2 has the actual values. I want to copy and transpose the dates into a new field in column Y for each populated row and name it Purchase Date beginning with the first populated record and then bring the corresponding values for each date into column Z naming it Purchase Amount. If you can help I would greatly appreciate it. Sample data is below.
VENDOR NUMBER
VENDOR NAME
MATERIAL
DESCRIPTION
VENDOR PART NUMBER
UOM
Lead Time
QTY ON ORDER
QTY FCST (12 MO)
TOTAL QTY
Buyer
MATERIAL CONTROLLER
Oct-16
Nov-16
Dec-16
Jan-17
Feb-17
Mar-17
Apr-17
May-17
Jun-17
Jul-17
Aug-17
Sep-17
103326
Palole Corporation DBA
6000060
RES MEP-HYPERCEL
12035
L
169
200
360
560
606
R10
0
0
130
0
0
70
0
0
150
0
0
210
103326
Palole Corporation DBA
6000124
FLT MEMBRANE CASSETTE 30K MWCO
OS030C26
EA
61
195
0
195
606
R06
0
0
0
95
40
0
0
0
0
60
0
0
103326
Palole Corporation DBA
6000165
FLT CART 5" 0.2UM HYDROPHILIC
AB05DFL2PH4
EA
50
0
24
24
606
R06
0
0
0
0
0
0
6
0
0
6
12
0
103326
Palole Corporation DBA
6000167
FLT CASSETE 30K MWCO 1FT2
OS030C12
EA
62
0
12
12
606
R06
0
0
0
0
0
12
0
0
0
0
0
0
103326
Palole Corporation DBA
6000196
FLT CAPSULE 0.2UM 500CM2 STERI
12991
EA
74
0
12
12
606
R06
0
0
0
12
0
0
0
0
0
0
0
0
103326
Palole Corporation DBA
6000414
FLT MEM CASETTE OMEGA T-SERIES
OS010T06
EA
64
61
14
75
606
R06
6
2
33
20
0
0
0
0
14
0
0
0
103326
Palole Corporation DBA
6001060
FLT VENT 0.2UM PTFE HYDROPHOBI
4251
EA
70
0
59
59
606
R06
0
0
0
0
0
17
0
0
15
0
27
0
103326
Palole Corporation DBA
6001345
FLT T-SERIES 30KD MWCO 2.5M2 M
OS030T26
EA
64
36
26
62
606
R06
0
0
36
0
0
8
0
0
0
0
0
18
103393
Dill Palmer Instrument Co
6001166
ADP Y CONNECTOR 3/8" BARB KYNA
WU-30703-93
EA
19
0
760
760
606
R04
0
0
0
0
230
0
0
220
0
0
310
0
104008
Signal-Abbot Inc.
6000055
CHM TRANS-4 5-DIHYDROXY-1 2-DI
D3511
G
51
14,000
24,876
38,876
606
R01
0
14,000
0
4,876
0
0
10,000
0
5,000
0
5,000
0
104008
Signal-Abbot Inc.
6000069
CHM DEXTRAN SULFATESODIUM SALT
D6924
G
51
4,000
8,450
12,450
606
R01
0
2,000
2,000
0
3,000
0
0
0
2,750
0
2,700
0
107575
Univarlist Usa Inc
6000087
CHM ANTIFOAM-C MED GRADE SILIC
803579
G
20
0
906
906
605
R01
0
0
0
0
0
0
906
0
0
0
0
0

<tbody>
</tbody>
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Anita Kay,

Welcome to the MrExcel forum.


We would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots, of the raw data, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


Or, you can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com
 
Upvote 0
I am including a link to dropbox and a file that will show you the data and the new layout I want to achieve.

https://dl.dropboxusercontent.com/u/40516771/Mr.Excel%20Forum/Anita%20Kay%20Sample%20Data.xlsx

Thanks!
Anita Kay said:
Hello all,

I have been coming the archives of many forums trying to find a good, effective and safe solution for changing the layout of an SAP report exported to Excel 2010. It takes months and months to get them to provide us with information and while we have asked for raw data they seem hesitant to give it to us. They always want to provide us with a report. I am not proficient with VBA but can get around. I do not understand a lot about looping.

What I need to do it to take the spreadsheet and insert twelve rows after each existing record, copy the information from columns A:L into the blanks beneath each populated row. I do that often with GoTo, Special, Find Blanks and a simple formula like =A2. The real problem lies with M:X. The reports is given with twelve months of data laid out in each column. Row 1 is the Date, row 2 has the actual values. I want to copy and transpose the dates into a new field in column Y for each populated row and name it Purchase Date beginning with the first populated record and then bring the corresponding values for each date into column Z naming it Purchase Amount. If you can help I would greatly appreciate it. Sample data is below.
VENDOR NUMBERVENDOR NAMEMATERIALDESCRIPTIONVENDOR PART NUMBERUOMLead TimeQTY ON ORDERQTY FCST (12 MO)TOTAL QTYBuyerMATERIAL CONTROLLEROct-16Nov-16Dec-16Jan-17Feb-17Mar-17Apr-17May-17Jun-17Jul-17Aug-17Sep-17
103326Palole Corporation DBA6000060RES MEP-HYPERCEL12035L169200360560606R100013000700015000210
103326Palole Corporation DBA6000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06000954000006000
103326Palole Corporation DBA6000165FLT CART 5" 0.2UM HYDROPHILICAB05DFL2PH4EA5002424606R060000006006120
103326Palole Corporation DBA6000167FLT CASSETE 30K MWCO 1FT2OS030C12EA6201212606R060000012000000
103326Palole Corporation DBA6000196FLT CAPSULE 0.2UM 500CM2 STERI12991EA7401212606R060001200000000
103326Palole Corporation DBA6000414FLT MEM CASETTE OMEGA T-SERIESOS010T06EA64611475606R06623320000014000
103326Palole Corporation DBA6001060FLT VENT 0.2UM PTFE HYDROPHOBI4251EA7005959606R06000001700150270
103326Palole Corporation DBA6001345FLT T-SERIES 30KD MWCO 2.5M2 MOS030T26EA64362662606R0600360080000018
103393Dill Palmer Instrument Co6001166ADP Y CONNECTOR 3/8" BARB KYNAWU-30703-93EA190760760606R04000023000220003100
104008Signal-Abbot Inc.6000055CHM TRANS-4 5-DIHYDROXY-1 2-DID3511G5114,00024,87638,876606R01014,00004,8760010,00005,00005,0000
104008Signal-Abbot Inc.6000069CHM DEXTRAN SULFATESODIUM SALTD6924G514,0008,45012,450606R0102,0002,00003,0000002,75002,7000
107575Univarlist Usa Inc6000087CHM ANTIFOAM-C MED GRADE SILIC803579G200906906605R0100000090600000

<tbody>
</tbody>
Click to expand...
 
Upvote 0
Anita Kay,

Thanks for the workbook/worksheets.

There seems to be a mistake/error in the number of rows for each group, going from the Sample Data worksheet, to the New Layout worksheet.



Excel 2007
LMNOPQRSTUVWX
1MATERIAL CONTROLLEROct-16Nov-16Dec-16Jan-17Feb-17Mar-17Apr-17May-17Jun-17Jul-17Aug-17Sep-17
2R100013000700015000210
Sample Data



Excel 2007
JK
1MATERIAL CONTROLLERProject Date
2R10Oct-16
3R10Nov-16
4R10Dec-16
5R10Jan-17
6R10Feb-17
7R10Mar-17
8R10Apr-17
9R10May-17
10R10Jun-17
11R10Jul-17
12R10Aug-17
13R10Sep-17
14R10Oct-16
New Layout


The New Layout worksheet contains 13 rows with the date Oct-16 duplicated 2 times. Is that correct?
 
Last edited:
Upvote 0
So sorry, I could have made a mistake. since each vendor already has one row, I really only need to insert 11 rows for each. I did this mockup late while trying to concentrate on another project.


hiker95 said:
Anita Kay,

Thanks for the workbook/worksheets.

There seems to be a mistake/error in the number of rows for each group, going from the Sample Data worksheet, to the New Layout worksheet.


Excel 2007
LMNOPQRSTUVWX
1MATERIAL CONTROLLEROct-16Nov-16Dec-16Jan-17Feb-17Mar-17Apr-17May-17Jun-17Jul-17Aug-17Sep-17
2R100013000700015000210

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sample Data​



Excel 2007
JK
1MATERIAL CONTROLLERProject Date
2R10Oct-16
3R10Nov-16
4R10Dec-16
5R10Jan-17
6R10Feb-17
7R10Mar-17
8R10Apr-17
9R10May-17
10R10Jun-17
11R10Jul-17
12R10Aug-17
13R10Sep-17
14R10Oct-16

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
New Layout​



The New Layout worksheet contains 13 rows with the date Oct-16 duplicated 2 times. Is that correct?
Click to expand...
 
Upvote 0
I should clarify that each vendor will have twelve monthly periods, some with values of zero. If you see the months repeated it should be because it is a different vendor.

Thanks!

Anita Kay said:
So sorry, I could have made a mistake. since each vendor already has one row, I really only need to insert 11 rows for each. I did this mockup late while trying to concentrate on another project.
Click to expand...
 
Upvote 0
Anita Kay,

Here is macro for you to consider, that uses two arrays in memory, and, should be very fast.

You can change the worksheet names in the macro.

I assume that both worksheets already exist, and, that the New Layout worksheet contains titles in row 1.

Sample raw data worksheet in two screenshots to fit the MrExcel display area:


Excel 2007
ABCDEFGHI
1VENDOR NUMBERVENDOR NAMEMATERIALDESCRIPTIONVENDOR PART NUMBERUOMLead TimeQTY ON ORDERQTY FCST (12 MO)
2103326Palole Corporation DBA6000060RES MEP-HYPERCEL12035L169200360
3103326Palole Corporation DBA6000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950
4103326Palole Corporation DBA6000165FLT CART 5" 0.2UM HYDROPHILICAB05DFL2PH4EA50024
5103326Palole Corporation DBA6000167FLT CASSETE 30K MWCO 1FT2OS030C12EA62012
6103326Palole Corporation DBA6000196FLT CAPSULE 0.2UM 500CM2 STERI12991EA74012
7103326Palole Corporation DBA6000414FLT MEM CASETTE OMEGA T-SERIESOS010T06EA646114
8103326Palole Corporation DBA6001060FLT VENT 0.2UM PTFE HYDROPHOBI4251EA70059
9103326Palole Corporation DBA6001345FLT T-SERIES 30KD MWCO 2.5M2 MOS030T26EA643626
10103393Dill Palmer Instrument Co6001166ADP Y CONNECTOR 3/8" BARB KYNAWU-30703-93EA190760
11104008Signal-Abbot Inc.6000055CHM TRANS-4 5-DIHYDROXY-1 2-DID3511G5114,00024,876
12104008Signal-Abbot Inc.6000069CHM DEXTRAN SULFATESODIUM SALTD6924G514,0008,450
13107575Univarlist Usa Inc6000087CHM ANTIFOAM-C MED GRADE SILIC803579G200906
14
Sample Data



Excel 2007
JKLMNOPQRSTUVWX
1TOTAL QTYBuyerMATERIAL CONTROLLEROct-16Nov-16Dec-16Jan-17Feb-17Mar-17Apr-17May-17Jun-17Jul-17Aug-17Sep-17
2560606R100013000700015000210
3195606R06000954000006000
424606R060000006006120
512606R060000012000000
612606R060001200000000
775606R06623320000014000
859606R06000001700150270
962606R0600360080000018
10760606R04000023000220003100
1138,876606R01014,00004,8760010,00005,00005,0000
1212,450606R0102,0002,00003,0000002,75002,7000
13906605R0100000090600000
14
Sample Data




Excel 2007
ABCDEFGHIJKLM
1MATERIALDESCRIPTIONVENDOR PART NUMBERUOMLead TimeQTY ON ORDERQTY FCST (12 MO)TOTAL QTYBuyerMATERIAL CONTROLLERProject DateActual Quantity
2
3
4
5
6
7
8
9
New Layout


And, after the macro (not all rows are shown for brevity):


Excel 2007
ABCDEFGHIJKL
1MATERIALDESCRIPTIONVENDOR PART NUMBERUOMLead TimeQTY ON ORDERQTY FCST (12 MO)TOTAL QTYBuyerMATERIAL CONTROLLERProject DateActual Quantity
26000060RES MEP-HYPERCEL12035L169200360560Oct-160
36000060RES MEP-HYPERCEL12035L169200360560Nov-160
46000060RES MEP-HYPERCEL12035L169200360560Dec-16130
56000060RES MEP-HYPERCEL12035L169200360560Jan-170
66000060RES MEP-HYPERCEL12035L169200360560Feb-170
76000060RES MEP-HYPERCEL12035L169200360560Mar-1770
86000060RES MEP-HYPERCEL12035L169200360560Apr-170
96000060RES MEP-HYPERCEL12035L169200360560May-170
106000060RES MEP-HYPERCEL12035L169200360560Jun-17150
116000060RES MEP-HYPERCEL12035L169200360560Jul-170
126000060RES MEP-HYPERCEL12035L169200360560Aug-170
136000060RES MEP-HYPERCEL12035L169200360560Sep-17210
146000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195Oct-160
156000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195Nov-160
166000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195Dec-160
176000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195Jan-1795
186000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195Feb-1740
196000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195Mar-170
206000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195Apr-170
216000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195May-170
226000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195Jun-170
236000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195Jul-1760
246000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195Aug-170
256000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195Sep-170
New Layout



See my next reply for the macro code.
 
Upvote 0
Anita Kay,

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Sub Reorg_SAP_Report()
' hiker95, 01/27/2017, ME987801
Dim a As Variant, i As Long, c As Long, cc As Long, d As Long, lr As Long, lc As Long
Dim o As Variant, j As Long, n As Long
Application.ScreenUpdating = False
With Sheets("Sample Data")    '<-- you can change the sheet name here
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  a = .Range(.Cells(1, 1), .Cells(lr, lc))
  ReDim o(1 To (lr + 1) * 12, 1 To 12)
End With
For i = 2 To UBound(a, 1) Step 1
  cc = 13
  For n = 1 To 12 Step 1
    j = j + 1
    d = 1
    For c = 3 To 10 Step 1
      o(j, d) = a(i, c)
      d = d + 1
    Next c
    o(j, 11) = a(1, cc)
    o(j, 12) = a(i, cc)
    cc = cc + 1
  Next n
Next i
With Sheets("New Layout")    '<-- you can change the sheet name here
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  If lr > 1 Then
    .Range("A2:L" & lr).ClearContents
  End If
  .Range("A2").Resize(UBound(o, 1), UBound(o, 2)) = o
  .Range("K2").Resize(UBound(o, 1)).NumberFormat = "mmm-yy"
  .Columns(1).Resize(, UBound(o, 2)).AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the Reorg_SAP_Report macro.
 
Upvote 0
Anita Kay,

Be back later for a fix for the New Layout worksheet, columns I, and, J.
 
Upvote 0
Anita Kay,

Sample new results in worksheet New Layout:


Excel 2007
ABCDEFGHIJKL
1MATERIALDESCRIPTIONVENDOR PART NUMBERUOMLead TimeQTY ON ORDERQTY FCST (12 MO)TOTAL QTYBuyerMATERIAL CONTROLLERProject DateActual Quantity
26000060RES MEP-HYPERCEL12035L169200360560606R10Oct-160
36000060RES MEP-HYPERCEL12035L169200360560606R10Nov-160
46000060RES MEP-HYPERCEL12035L169200360560606R10Dec-16130
56000060RES MEP-HYPERCEL12035L169200360560606R10Jan-170
66000060RES MEP-HYPERCEL12035L169200360560606R10Feb-170
76000060RES MEP-HYPERCEL12035L169200360560606R10Mar-1770
86000060RES MEP-HYPERCEL12035L169200360560606R10Apr-170
96000060RES MEP-HYPERCEL12035L169200360560606R10May-170
106000060RES MEP-HYPERCEL12035L169200360560606R10Jun-17150
116000060RES MEP-HYPERCEL12035L169200360560606R10Jul-170
126000060RES MEP-HYPERCEL12035L169200360560606R10Aug-170
136000060RES MEP-HYPERCEL12035L169200360560606R10Sep-17210
146000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Oct-160
156000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Nov-160
166000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Dec-160
176000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Jan-1795
186000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Feb-1740
196000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Mar-170
206000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Apr-170
216000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06May-170
226000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Jun-170
236000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Jul-1760
246000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Aug-170
256000124FLT MEMBRANE CASSETTE 30K MWCOOS030C26EA611950195606R06Sep-170
New Layout





Code: 
Sub Reorg_SAP_Report_V2()
' hiker95, 01/27/2017, ME987801
Dim a As Variant, i As Long, c As Long, cc As Long, d As Long, lr As Long, lc As Long
Dim o As Variant, j As Long, n As Long
Application.ScreenUpdating = False
With Sheets("Sample Data")    '<-- you can change the sheet name here
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  a = .Range(.Cells(1, 1), .Cells(lr, lc))
  ReDim o(1 To (lr + 1) * 12, 1 To 12)
End With
For i = 2 To UBound(a, 1) Step 1
  cc = 13
  For n = 1 To 12 Step 1
    j = j + 1
    d = 1
    For c = 3 To 12 Step 1
      o(j, d) = a(i, c)
      d = d + 1
    Next c
    o(j, 11) = a(1, cc)
    o(j, 12) = a(i, cc)
    cc = cc + 1
  Next n
Next i
With Sheets("New Layout")    '<-- you can change the sheet name here
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  If lr > 1 Then
    .Range("A2:L" & lr).ClearContents
  End If
  .Range("A2").Resize(UBound(o, 1), UBound(o, 2)) = o
  .Range("K2").Resize(UBound(o, 1)).NumberFormat = "mmm-yy"
  .Columns(1).Resize(, UBound(o, 2)).AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Same instructions per my reply #8.

Then run the Reorg_SAP_Report_V2 macro.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA Loop to clear contents of fiscal year rows in specific month columns
Replies
6
Views
504
kevin9999
K

Forum statistics

Threads
1,215,217
Messages
6,123,670
Members
449,115
Latest member
punka6

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