I have a single row of data made up of thousands of columns. Every 14 columns of data makes up a product in my point of sale system. I would like each product (14 columns) to be on it's own row. How can I do this?
I have a single row of data made up of thousands of columns. Every 14 columns of data makes up a product in my point of sale system. I would like each product (14 columns) to be on it's own row. How can I do this?
Assumes the row of information is in row 1
Please Login or Register to view this content.
grimreefer,
Welcome to the Excel Forum.
If your raw data is in row 1, beginning in cell A1, then all your data will be moved, beginning in cell A3, down (leaving all your data in row 1 intact).
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).
Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your 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. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.
Please Login or Register to view this content.
Then run the "Move14" macro.
Have a great day,
Stan
Windows 10, Excel 2007, on a PC.
If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
Or a simple formula way, if your data is on row 1:
=INDEX($1:$1,ROWS($2:2)*14+COLUMN()) in A2, copied over and down as needed. Copy and paste as values when done and delete the original data (after column N, of course).
Last edited by darkyam; 03-23-2010 at 11:51 PM.
Thank you!!!!
Hello stanleydgromjr,
This is great. I can use this for something similar but it will need some tweaking. I have multiple rows with 19 columns each and I need to break each row into 7,6 and 6 columns. Could you please suggest the modifications in this code. I really appreciate your help.
Thanks!
kodwanivijay,
Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks