Rearranging data for import

U

Undertegnede

New Member
Joined
Oct 15, 2013
Messages
15
Office Version
  1. 365
Platform
  1. Windows
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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Activity[/TD]
[TD]201701[/TD]
[TD]201702[/TD]
[TD]201703[/TD]
[TD]201704[/TD]
[TD]201705[/TD]
[TD]201706[/TD]
[TD]201707[/TD]
[TD]201708[/TD]
[TD]201709[/TD]
[TD]201710[/TD]
[TD]201711[/TD]
[TD]201712[/TD]
[/TR]
[TR]
[TD]Activity 1
[/TD]
[TD]5000[/TD]
[TD]5000[/TD]
[TD]5000[/TD]
[TD]5000[/TD]
[TD]5000[/TD]
[TD]5000[/TD]
[TD]5000[/TD]
[TD]5000[/TD]
[TD]5000[/TD]
[TD]5000[/TD]
[TD]5000[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]Activity 2[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]3000[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]4000[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]2000[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]Activity 3[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]6000[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Desired output:

[TABLE="width: 300"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Activity 1[/TD]
[TD]201701[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]Activity 1[/TD]
[TD]201702[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]Activity 1[/TD]
[TD]201703[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]Activity 1[/TD]
[TD]201704[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]Activity 1[/TD]
[TD]201705[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]Activity 1[/TD]
[TD]201706[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]Activity 1[/TD]
[TD]201707[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]Activity 1[/TD]
[TD]201708[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]Activity 1[/TD]
[TD]201709[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]Activity 1[/TD]
[TD]201710[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]Activity 1[/TD]
[TD]201711[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]Activity 1[/TD]
[TD]201712[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]Activity 2[/TD]
[TD]201701[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Activity 2[/TD]
[TD]201702[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Activity 2[/TD]
[TD]201703[/TD]
[TD]3000[/TD]
[/TR]
[TR]
[TD]Activity 2[/TD]
[TD]201704[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Activity 2[/TD]
[TD]201705[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Activity 2[/TD]
[TD]201706[/TD]
[TD]4000[/TD]
[/TR]
[TR]
[TD]Activity 2[/TD]
[TD]201707[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Activity 2[/TD]
[TD]201708[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Activity 2[/TD]
[TD]201709[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]Activity 2[/TD]
[TD]201710[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Activity 2[/TD]
[TD]201711[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Activity 2[/TD]
[TD]201712[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]Activity 3[/TD]
[TD]201701[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Activity 3[/TD]
[TD]201702[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Activity 3[/TD]
[TD]201703[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Activity 3[/TD]
[TD]201704[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Activity 3[/TD]
[TD]201705[/TD]
[TD]6000[/TD]
[/TR]
[TR]
[TD]Activity 3[/TD]
[TD]201706[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Activity 3[/TD]
[TD]201707[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Activity 3[/TD]
[TD]201708[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Activity 3[/TD]
[TD]201709[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Activity 3[/TD]
[TD]201710[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Activity 3[/TD]
[TD]201711[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Activity 3[/TD]
[TD]201712[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I would appreciate any input towards a solution.

Best regards.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
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.

Code: 
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.
 
Upvote 0
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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Account[/TD]
[TD]Location[/TD]
[TD]Segment[/TD]
[TD]Activity[/TD]
[TD]201701[/TD]
[TD]201702[/TD]
[TD]201703[/TD]
[TD]201704[/TD]
[TD]201705[/TD]
[TD]201706[/TD]
[TD]201707[/TD]
[TD]201708[/TD]
[TD]201709[/TD]
[TD]201710[/TD]
[TD]201711[/TD]
[TD]201712[/TD]
[/TR]
[TR]
[TD]7132[/TD]
[TD]6000[/TD]
[TD]100[/TD]
[TD]Activity 1[/TD]
[TD]1000[/TD]
[TD]2000[/TD]
[TD]3000[/TD]
[TD]4000[/TD]
[TD]5000[/TD]
[TD]6000[/TD]
[TD]7000[/TD]
[TD]8000[/TD]
[TD]9000[/TD]
[TD]10000[/TD]
[TD]11000[/TD]
[TD]12000[/TD]
[/TR]
</tbody>[/TABLE]


Output:
[TABLE="width: 500"]
<tbody>[TR]
[TD]7132[/TD]
[TD]6000[/TD]
[TD]100[/TD]
[TD]Activity 1[/TD]
[TD]201701[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]7132[/TD]
[TD]6000[/TD]
[TD]100[/TD]
[TD]Activity 1[/TD]
[TD]201701[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]7132[/TD]
[TD]6000[/TD]
[TD]100[/TD]
[TD]Activity 1[/TD]
[TD]201701[/TD]
[TD]3000[/TD]
[/TR]
[TR]
[TD]7132[/TD]
[TD]6000[/TD]
[TD]100[/TD]
[TD]Activity 1[/TD]
[TD]201701[/TD]
[TD]4000[/TD]
[/TR]
[TR]
[TD]7132[/TD]
[TD]6000[/TD]
[TD]100[/TD]
[TD]Activity 1[/TD]
[TD]201701[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]7132[/TD]
[TD]6000[/TD]
[TD]100[/TD]
[TD]Activity 1[/TD]
[TD]201701[/TD]
[TD]6000[/TD]
[/TR]
[TR]
[TD]7132[/TD]
[TD]6000[/TD]
[TD]100[/TD]
[TD]Activity 1[/TD]
[TD]201701[/TD]
[TD]7000[/TD]
[/TR]
[TR]
[TD]7132[/TD]
[TD]6000[/TD]
[TD]100[/TD]
[TD]Activity 1[/TD]
[TD]201701[/TD]
[TD]8000[/TD]
[/TR]
[TR]
[TD]7132[/TD]
[TD]6000[/TD]
[TD]100[/TD]
[TD]Activity 1[/TD]
[TD]201701[/TD]
[TD]9000[/TD]
[/TR]
[TR]
[TD]7132[/TD]
[TD]6000[/TD]
[TD]100[/TD]
[TD]Activity 1[/TD]
[TD]201701[/TD]
[TD]10000[/TD]
[/TR]
[TR]
[TD]7132[/TD]
[TD]6000[/TD]
[TD]100[/TD]
[TD]Activity 1[/TD]
[TD]201701[/TD]
[TD]11000[/TD]
[/TR]
[TR]
[TD]7132[/TD]
[TD]6000[/TD]
[TD]100[/TD]
[TD]Activity 1[/TD]
[TD]201701[/TD]
[TD]12000[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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.
Click to expand...

Undertegnede,

Be back in a little while.
 
Upvote 0
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.

Code: 
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.
 
Last edited:
Upvote 0
Undertegnede,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.
 
Upvote 0
hiker95 said:
Undertegnede,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.
Click to expand...

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
 
Upvote 0
I was wondering if you (or someone else) could create a code to revert the data from the output-format into the input format?
Click to expand...

Undertegnede,

Please explain why you would want to do the above?
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
vba help - merge two range data into PDF printable area
Replies
1
Views
405
Mallesh23
M
I
  • Question Question
How to choose specific data on a dropdown element with >input element
Replies
0
Views
3K
ivankesaulya
I
Ombir
  • Question Question
Date Difference with year and month (Formulae)
Replies
4
Views
739
Ombir
Ombir
U
  • Question Question
VBA - Copy Row Data Based on Criteria
Replies
2
Views
1K
unknownymous
U
M
  • Question Question
Structuring data correctly to use in pivottable
Replies
9
Views
641
sandy666
sandy666

Forum statistics

Threads
1,223,908
Messages
6,175,305
Members
452,633
Latest member
DougMo

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