Rearranging data for import

[Undertegnede]

Hello,

I am looking for a clever way to rearrange data in order to be able to import to a financial system (unit 4 / Agresso).

The input:

Activity	201701	201702	201703	201704	201705	201706	201707	201708	201709	201710	201711	201712
Activity 1	5000	5000	5000	5000	5000	5000	5000	5000	5000	5000	5000	5000
Activity 2			3000			4000			2000			1000
Activity 3					6000

<tbody>
</tbody>

Desired output:

Activity 1	201701	5000
Activity 1	201702	5000
Activity 1	201703	5000
Activity 1	201704	5000
Activity 1	201705	5000
Activity 1	201706	5000
Activity 1	201707	5000
Activity 1	201708	5000
Activity 1	201709	5000
Activity 1	201710	5000
Activity 1	201711	5000
Activity 1	201712	5000
Activity 2	201701
Activity 2	201702
Activity 2	201703	3000
Activity 2	201704
Activity 2	201705
Activity 2	201706	4000
Activity 2	201707
Activity 2	201708
Activity 2	201709	2000
Activity 2	201710
Activity 2	201711
Activity 2	201712	1000
Activity 3	201701
Activity 3	201702
Activity 3	201703
Activity 3	201704
Activity 3	201705	6000
Activity 3	201706
Activity 3	201707
Activity 3	201708
Activity 3	201709
Activity 3	201710
Activity 3	201711
Activity 3	201712

<tbody>
</tbody>

I would appreciate any input towards a solution.

Best regards.

 

[hiker95]

Undertegnede,

Welcome to the MrExcel forum.

Here is a macro solution for you to consider that uses two arrays in memory, and, will adjust to the number of raw data rows, and, columns.

I assume that the raw data worksheet, Input, and, the resulting worksheet, Output, already exist.

You can change the worksheet names in the macro.


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.

Sub RearrangeData()
' hiker95, 09/19/2017, ME1023287
Application.ScreenUpdating = False
Dim wi As Worksheet, wo As Worksheet
Dim a As Variant, i As Long, c As Long
Dim o As Variant, j As Long
Set wi = Sheets("Input")    '<-- you can change the sheet name here
Set wo = Sheets("Output")   '<-- you can change the sheet name here
With wi
  a = wi.Cells(1, 1).CurrentRegion
  ReDim o(1 To UBound(a, 1) * UBound(a, 2), 1 To 3)
End With
For i = 2 To UBound(a, 1) Step 1
  For c = 2 To UBound(a, 2) Step 1
    j = j + 1: o(j, 1) = a(i, 1): o(j, 2) = a(1, c): o(j, 3) = a(i, c)
  Next c
Next i
With wo
  .UsedRange.ClearContents
  .Cells(1, 1).Resize(UBound(o, 1), UBound(o, 2)) = o
  .UsedRange.Columns.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 RearrangeData macro.

 

[Undertegnede]

Code worked perfectly. Many thanks for the quick and precise reply.

Could I also bother you or someone with an addition that will copy a few more columns?
I should be able to do this my self based on your code, but my coding skills are not that great.

Input:

Account	Location	Segment	Activity	201701	201702	201703	201704	201705	201706	201707	201708	201709	201710	201711	201712
7132	6000	100	Activity 1	1000	2000	3000	4000	5000	6000	7000	8000	9000	10000	11000	12000

<tbody>
</tbody>

Output:

7132	6000	100	Activity 1	201701	1000
7132	6000	100	Activity 1	201701	2000
7132	6000	100	Activity 1	201701	3000
7132	6000	100	Activity 1	201701	4000
7132	6000	100	Activity 1	201701	5000
7132	6000	100	Activity 1	201701	6000
7132	6000	100	Activity 1	201701	7000
7132	6000	100	Activity 1	201701	8000
7132	6000	100	Activity 1	201701	9000
7132	6000	100	Activity 1	201701	10000
7132	6000	100	Activity 1	201701	11000
7132	6000	100	Activity 1	201701	12000

<tbody>
</tbody>

 

[hiker95]

Code worked perfectly. Many thanks for the quick and precise reply.

Undertegnede,

Thanks for the feedback.

You are very welcome. Glad I could help.

 

[hiker95]

Could I also bother you or someone with an addition that will copy a few more columns?
I should be able to do this my self based on your code, but my coding skills are not that great.

Undertegnede,

Be back in a little while.

 

[hiker95]

Undertegnede,

Here is a new macro solution for you to consider, that is based on your new raw data structure, that uses two arrays in memory, and, will adjust to the number of raw data rows, and, columns.

I assume that the raw data worksheet, Input, and, the resulting worksheet, Output, already exist.

You can change the worksheet names in the macro.

With the same instructions as my reply #2.

Sub RearrangeData_V2()
' hiker95, 09/19/2017, ME1023287
Application.ScreenUpdating = False
Dim wi As Worksheet, wo As Worksheet
Dim a As Variant, i As Long, c As Long
Dim o As Variant, j As Long
Set wi = Sheets("Input")    '<-- you can change the sheet name here
Set wo = Sheets("Output")   '<-- you can change the sheet name here
With wi
  a = wi.Cells(1, 1).CurrentRegion
  ReDim o(1 To (UBound(a, 1) - 1) * (UBound(a, 2) - 4), 1 To 6)
End With
For i = 2 To UBound(a, 1) Step 1
  For c = 5 To UBound(a, 2) Step 1
    j = j + 1
    o(j, 1) = a(i, 1): o(j, 2) = a(i, 2): o(j, 3) = a(i, 3): o(j, 4) = a(i, 4)
    o(j, 5) = a(1, c): o(j, 6) = a(i, c)
  Next c
Next i
With wo
  .UsedRange.ClearContents
  .Cells(1, 1).Resize(UBound(o, 1), UBound(o, 2)) = o
  .UsedRange.Columns.AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

With the same instructions as my reply #2.

Then run the RearrangeData_V2 macro.

 

[Undertegnede]

Again spot on, thank you hiker95

 

[hiker95]

Undertegnede,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[Undertegnede]

Undertegnede,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

Hi again, Hiker95,

It might be a long shot after quite some time, but I was wondering if you (or someone else) could create a code to revert the data from the output-format into the input format?

Best regards,
Arild

 

[hiker95]

I was wondering if you (or someone else) could create a code to revert the data from the output-format into the input format?

Undertegnede,

Please explain why you would want to do the above?