We help IT Professionals succeed at work.

Macro for changing structure of data in Excel

143 Views
Last Modified: 2018-01-17
I have a spreadsheet that contains extracted data from another source, and I need to change the structure of the data to a more usable format for output to a pivot table. As I will be receiving the data in this format on a regular basis and need to perform this restructuring task every time, I would like to automate this task with a macro.

Spreadsheet description:
Each WO task has same 5 column headings: WO#, WO_Type, Date, Min, and Min_Type, and the WO numbers are repeated in each 5-column section across the spreadsheet:
Column A to E: WO task 1
Column F to J: WO task 2
Column K to O: WO task 3
Column P to T: WO task 4

While the current spreadsheet has 455 rows, the number of rows will vary each time.

I would like a macro that does the following:
  • Copies data from A2 to E2 and down to last row containing data, and pastes this data into B2 of table in "Tasks" tab.
  • Copies data from F2 to J2 down to last row containing data, and pastes this date into next available blank row in Column B of table in "Tasks" tab
  • Copies data from K2 to O2 down to last row containing data, and pastes this date into next available blank row in Column B of table in "Tasks" tab
  • Copies data from P2 to T2 down to last row containing data, and pastes this date into next available blank row in Column B of table in "Tasks" tab

I have attached the spreadsheet with sample data.

Thanks,
Andrea
EE_RestructureData.xlsm
Comment
Watch Question

Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
I think this does what you want, please check and let me know if it needs any revision.

Note I have removed all the unused rows in the destination table as this makes it extremely difficult to calculate where to paste the data. I have replaced CFS with ="CFS" to ensure this is copied down as data is added.
EE_RestructureData.xlsm

Author

Commented:
Hi Roy,

Thanks very much for improving the process as outlined. I ran the macro, and it transferred all the records from Column A to E (WO task 1), which was good, but it did not appear to transfer the remaining records for WO task 2 (Column F to J), etc. through to WO task 4. Sheet 1 should end up with 1816 rows of data, I believe.

Can the macro be revised to do this?

Cheers,
Andrea
Managing Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Roy CoxGroup Finance Manager
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
Thanks to you both!

Roy, thanks for the revised macro, it worked well with a couple of minor issues - the first row was blank, for some reason, and the date field came in numerically as opposed to formatted as a date.

Shums, your solution worked perfectly.

I did my best to award the points fairly, balancing Roy's quick response time and willingness to revise the first iteration vs. Shum's solution working right out of the gate. :-)

Best regards,
Andrea

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions