Kindly please help to convert from Horizontal To Vertical Format. Its about 1000 rows The sample data are,
Item Desc Item2 Desc2 Qty
1 A 1A 1AA 1
1 A 1B 1BB 2
1 A 1C 1CC 3
2 B 2A 2AB 1
2 B 2B 2BB 7
3 C 3C 3CC 10
Results Required will be,
Item Desc Item2 Desc2 Qty Item2 Desc2 Qty Item2 Desc2 Qty
1 A 1A 1AA 1 1B 1BB 2 1C 1CC 3
2 B 2A 2AB 1 2B 2BB 7
3 C 3C 3CC 10
Thank you.
Last edited by VBA Noob; 04-03-2009 at 01:49 AM.
Hello masrizam,
Welcome to the Forum!
The following macro will merge like data into a single row with multiple columns. The data need not be sorted beforehand. You can specify the starting cell and the maximum number of columns that can be used. These are marked in red in the code.
Adding the Macro
Please Login or Register to view this content.
1. Copy the macro above 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. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.
To Run the Macro...
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.
Last edited by Leith Ross; 04-03-2009 at 02:32 AM.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
After testing, herewith the result
Item Desc Item2 Desc2 Qty
1 A A A
2 B B
3 C
But it not correct....
This macro will resort your data and put in the appropriate titles.
Please Login or Register to view this content.
Last edited by JBeaucaire; 04-03-2009 at 03:17 AM. Reason: Fixed the code for the titles, works ok now
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Thanks for your quick reply. If the number of row and column is not fixed. It is flexible. Is it can be done??? Thanks.
This is already flexible for row length, it starts at the bottom and works its way up, so however far down it goes it will work.
How many columns out do you imagine it needing? Here, I've tweaked one number so it will go out the right up to 100 columns. Is that sufficient?
Please Login or Register to view this content.
Thanks a lot. The data now is correct. The only problems is the title. It should show for every row.
I fixed the code shortly after posting it, if you are using the original code from the last post above, grab the version shown there now.
Re: Convert Excel From Horizontal To Vertical Format
Hi JB,
Can you help me to check on my attachments. There are 2 sheets "Source Data" and "Output Data". I already copied your Macro and execute it but if you check from the "Output Data" for row no. 1 some of the title was not shown. You can check item with yellow colour. Actually it should be same title for column C,D,E,F,G.
Hope you help me. Thanks a lot..
Post up a real sample workbook next time, that's my suggestion. The number of columns between your original post and this sheet don't appear to be the same.
Please Login or Register to view this content.
Hi JB,
Thanks for the solution. It solved already. By the way I got another sheet need to convert from vertical to horizontal. You can refer to my attachment. The details are,
1. There are 3 sheets SAP_ITEM_CODE, RAW DATA & OUTPUT DATA.
2. Refer to the OUTPUT DATA, all the Orange Fields will always refer to the SAP_ITEM_CODE base on the Item_Int & Item.
3. The Yellow Fields (Columns D,E & G) are hardcoded and Base Quantity Fields is refer to
RAW DATA sheet, refer to H1.
Thank you.
Phew...that one was a little more complicated, but give this macro a whirl:
Please Login or Register to view this content.
Last edited by JBeaucaire; 04-13-2009 at 01:39 AM. Reason: Corrected code for column F
Thanks JB, you are really good.
If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks