Move a few cells from one row into a new row... many many times.

[ansupe]

Hello!


Here is a "scrubbed" version of the spreadsheet I'm working from: https://dl.dropbox.com/u/89323524/STEP%20sample%20file.xlsx


I am a data specialist for a school system, and we're working on a massive data organization project that will eventually allow us to save and access our data in the cloud. As a result, I have to reformat the above-linked spreadsheet so that each row is converted into multiple rows - one for each "color block" of columns starting in Column K.


- A2:F2 should stay the same. Then the information in columns K thru N should be pasted into columns G thru J.

- Then, in the next row, repeat A2:F2 into row 3. Then put the information in the red columns (O:Q) into columns G:I, leaving column J empty.

- Then, in the next row, repeat A2:F2 into row 4. Then put the information in the orange columns (R:T) into columns G:I, leaving column J empty.


Repeat this pattern until all 18 colored column sets have been pasted into columns G thru J. In the end, each student should have 18 rows instead of just one (so "Alfred" will be in C2:C19, then "Ursula" in C20:C37, etc). The sheet called "Desired final product" shows how I want the data to look in the end.


The colors of the columns are arbitrary and do not need to be repeated when the contents are "relocated;" I formatted the sheet this way for my own sanity!


I have to repeat this process with thousands of records on multiple spreadsheets, each one with the same format but with varying numbers of rows and columns. It's clearly not feasible to copy and paste by hand! Does anyone have an idea for a macro or loop that might help? Thank you!!

 

[SirJB7]

Hi, Angie P!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


Give a look at this file:

https://dl.dropbox.com/u/60558749/Move%20a%20few%20cells%20from%20one%20row%20into%20a%20new%20row...%20many%20many%20times.%20-%20STEP%20sample%20file%20%28for%20Angie%20P%20at%20chandoo.org%29.xlsm


It has a module "Módulo1" which you should reply and copy in every workbook you need to reformat. You can do this by exporting the module and importing it for each workbook, or you can copy and paste the whole code (from the "Option Explicit" to the "End Sub" statements) into any module of each workbook.


Then simply run "ReformatSheet" macro.


Description:

a) it works with .xls, .xlsb, .xlsx and .xlsm Excel files: if any different, just advise

b) it creates a new workbook with the name <old_workbook>_new.<old_extension> for each workbook

c) it creates a new worksheet with the same name as original, for each worksheet in original workbook: if any worksheet should not be processed, just advise

d) it splits data as request: at least I hope so


Just advise if any issue.


Regards!