+ Reply to Thread
Results 1 to 24 of 24

Macro/Script for Moving Groups of Data in to Separate Columns

  1. #1
    Registered User
    Join Date
    07-04-2012
    Location
    Carbondale, IL
    MS-Off Ver
    Excel 2003
    Posts
    8

    Macro/Script for Moving Groups of Data in to Separate Columns

    But I'm going to try my best...

    So here's what I have. A huge list of data in 1 column like this

    datadatadata
    datadatadata
    datadatadata

    ..................(two rows of spacing before the next 3 lines of data)
    datadatadata
    datadatadata
    datadatadata


    datadatadata
    datadatadata
    datadatadata
    ___________________
    Okay, with showing you that terrible example, here's what I need to accomplish for work.
    I need to get those 3 rows of data in separate columns right next to it, like this:

    column1........................column2........................column3
    datadatadata.................datadatadata.................datadatadata
    datadatadata.................datadatadata.................datadatadata
    datadatadata.................datadatadata.................datadatadata
    ___________________
    Is this possible to do with a script or macro?
    The initial row can stay if its a problem, so it could look like

    column 1......................column 2.......................column 3........................column 4
    datadatadata................datadatadata.................datadatadata.................datadatadata
    datadatadata
    datadatadata


    datadatadata................datadatadata.................datadatadata.................datadatadata
    datadatadata
    datadatadata


    Or whatever - I could just delete the whole first column afterwards.
    It doesn't have to be perfect at all, I just have to get a faster way to do this than copy it in to the other rows manually, a script or macro that will do a whole sheet at once would be GREAT.
    Each one of these sheets have about 1600 + rows (2 spaces in between each of the groups of data) so you can see why I haveeee to find a better way.

    If someone could throw me a bone I'd be SUPER grateful - you have no idea how grateful lol.

    Thanks in advance for trying,
    Justin






    EDIT: ADDED Attachment as requested !
    Attached Files Attached Files
    Last edited by JustinSxcel; 07-04-2012 at 01:51 PM.

  2. #2
    Registered User
    Join Date
    06-19-2012
    Location
    Canada Eh
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    40

    Re: Very Hard To Explain Question

    Hi Justin, firstly, your title is not very good and you may be whipped by mods because of it. Secondly, I appreciate that your situation is difficult to explain but you'll need to do better than datadatadata. Perhaps you should upload a mock workbook with non-personal or sensitive data in it. Provide a clear and concise before and after scenario and I'm certain that someone here will be able to help. Good luck.
    EF killed Mordred

    46 & 2 is just ahead of me!

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Very Hard To Explain Question

    You would be better attaching an example worksheet
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  4. #4
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Very Hard To Explain Question

    Hello Justin and Welcome to the forum,

    Please update your title to something more meaningful and upload a sample file. I know what you are asking and it's not that hard to accomplish but a sample can go a long way to reaching the solution.
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

  5. #5
    Registered User
    Join Date
    07-04-2012
    Location
    Carbondale, IL
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Macro/Script for Moving Groups of Data in to Separate Columns

    Okay guys, I changed title to try to explain it lol! Sorry about that, first time poster and didn't read any rules (coming clean haha)

    So what I'm working with is an excel sheet at CSV format - but I can't upload a CSV file as an attachment to this site.
    Is there a preferred doc type you guys would like me to change it to to make it easier to convert back to CSV or whatever you have to do after I upload it?

    Thank you for all the responses!

  6. #6
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Macro/Script for Moving Groups of Data in to Separate Columns

    I think it might be a size problem. Zip the file first then upload.

  7. #7
    Registered User
    Join Date
    06-19-2012
    Location
    Canada Eh
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    40

    Re: Macro/Script for Moving Groups of Data in to Separate Columns

    Save it as an .xls file, that should be fine.

  8. #8
    Registered User
    Join Date
    07-04-2012
    Location
    Carbondale, IL
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Macro/Script for Moving Groups of Data in to Separate Columns

    Ahh already did the zip folder and got it to upload, its attached on first post!

  9. #9
    Valued Forum Contributor StevenM's Avatar
    Join Date
    03-23-2008
    Location
    New Lenox, IL USA
    MS-Off Ver
    2007
    Posts
    910

    Re: Macro/Script for Moving Groups of Data in to Separate Columns

    Please Login or Register  to view this content.
    Last edited by StevenM; 07-04-2012 at 01:57 PM. Reason: P.S. Changed to start on row 4.

  10. #10
    Registered User
    Join Date
    07-04-2012
    Location
    Carbondale, IL
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Macro/Script for Moving Groups of Data in to Separate Columns

    Steven!!!! Is that the code I need? Holy crap my boss is going to be very happy if it works lol

    I've never used scripts or whatever in Excel before, my boss suggested it. Where do I plug that in to get excel to run it?






    **Edited rewording
    Last edited by JustinSxcel; 07-04-2012 at 01:59 PM.

  11. #11
    Valued Forum Contributor StevenM's Avatar
    Join Date
    03-23-2008
    Location
    New Lenox, IL USA
    MS-Off Ver
    2007
    Posts
    910

    Re: Macro/Script for Moving Groups of Data in to Separate Columns

    See: Honda_ATV_Parts.xlsm

    The code in the attached file was modified to start on row 4.

  12. #12
    Registered User
    Join Date
    06-19-2012
    Location
    Canada Eh
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    40

    Re: Macro/Script for Moving Groups of Data in to Separate Columns

    Hi StevenM, I hope this is okay but I turned off and on screen updating to remove the flickering. Nice work with that though!
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    07-04-2012
    Location
    Carbondale, IL
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Macro/Script for Moving Groups of Data in to Separate Columns

    When I open the xlsm file you uploaded I had to upgrade to some 2007 files or whatever and I got that done, I can open the file now.

    But when I open it it gives error:
    "Macros in this workbook are disabled because the security level is high, and the macros have not been digitally signed or verified as save. To run the macros, you can either have them signed or change your security level."

    How to I change it so the macro works? lol

    Or still where can I run this script on my own sheet? Because there's still a lot of files to do (im using excel 2003)

  14. #14
    Registered User
    Join Date
    06-19-2012
    Location
    Canada Eh
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    40

    Re: Macro/Script for Moving Groups of Data in to Separate Columns

    Hi Justin, try alt + F11 and then click Insert --> Module and then past the code in.

  15. #15
    Valued Forum Contributor StevenM's Avatar
    Join Date
    03-23-2008
    Location
    New Lenox, IL USA
    MS-Off Ver
    2007
    Posts
    910

    Re: Macro/Script for Moving Groups of Data in to Separate Columns

    But when I open it it gives error:
    "Macros in this workbook are disabled because the security level is high, and the macros have not been digitally signed or verified as save. To run the macros, you can either have them signed or change your security level."
    Your Macro-Enabled Workbook needs to be in a folder which you can designate as macro friendly.

    For 2007 and later, the way you do this: click on the Office Button -> Excel Options -> Trust Center -> Trust Center Setting -> Trusted Locations. Then you add your folder (where you have your Excel Macro-Enabled Workbook) as a trusted location.

    For earlier editions of Excel, I don't know what one is supposed to do.

  16. #16
    Registered User
    Join Date
    07-04-2012
    Location
    Carbondale, IL
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Macro/Script for Moving Groups of Data in to Separate Columns

    It works!! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you!

    Now you said that it starts on line 4, that worked great for this document, what do I edit to make it start from the first row on the rest?

  17. #17
    Registered User
    Join Date
    07-04-2012
    Location
    Carbondale, IL
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Macro/Script for Moving Groups of Data in to Separate Columns

    I think I found what to change

    ' The first set of data starts at row 4

    to

    ' The first set of data starts at row 1

    then it should be good to go for the rest of the files, right?

  18. #18
    Registered User
    Join Date
    07-04-2012
    Location
    Carbondale, IL
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Macro/Script for Moving Groups of Data in to Separate Columns

    Does this look about right to start at line 1 on the rest of the documents?


    ' There are sets of three rows of data in column A followed by two blank rows.
    ' For each set, the second row belongs in column B; and the third row belongs in column C.
    ' The first set of data starts at row 1.
    Sub ModifyData()
    Dim nRow As Long, nLastrow As Long
    Application.ScreenUpdating = False
    nLastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For nRow = 1 To nLastrow Step 5
    Cells(nRow, "B") = Cells(nRow + 1, "A")
    Cells(nRow, "C") = Cells(nRow + 2, "A")
    Next nRow
    nLastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For nRow = nLastrow To 1 Step -1
    If IsEmpty(Cells(nRow, "B")) Then Rows(nRow).EntireRow.Delete
    Next nRow
    Application.ScreenUpdating = True
    End Sub

  19. #19
    Valued Forum Contributor StevenM's Avatar
    Join Date
    03-23-2008
    Location
    New Lenox, IL USA
    MS-Off Ver
    2007
    Posts
    910

    Re: Macro/Script for Moving Groups of Data in to Separate Columns

    If you need the code to start at row 1 rather than row 4, then change the two 4s to 1. It looks like you did it correctly.

  20. #20
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,338

    Re: Macro/Script for Moving Groups of Data in to Separate Columns

    Paste the code in a new workbook and save in the same folder where "Honda....csv" file is located.
    Then run the code.
    It will read directly from csv file, not from xls* file.
    Please Login or Register  to view this content.

  21. #21
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Macro/Script for Moving Groups of Data in to Separate Columns

    @jindon, I like the concept but can't get the array to be created on my system. I will try it on another computer.

  22. #22
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,338

    Re: Macro/Script for Moving Groups of Data in to Separate Columns

    Quote Originally Posted by abousetta View Post
    @jindon, I like the concept but can't get the array to be created on my system. I will try it on another computer.
    OK,
    It is working here perfectly.

    Win7, xl2010

  23. #23
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Macro/Script for Moving Groups of Data in to Separate Columns

    I'm on winXP and 2010. I burned my CPU at home (probably running too many weekend long analyses) so will have to wait to see what someone says.

  24. #24
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Macro/Script for Moving Groups of Data in to Separate Columns

    If you are using Excel 2003 or earlier make sure that macro security is set to Medium

    Tools - > Macros -> Security

    Then when opening workbooks with macros you are asked whether to enable them.

    Excel 200/2010

    Developer Tab -> Macro Security or set up a Trusted Folder

    Excel 2010 will remember your choice when opening a macro enable workbook & not prompt again.once you have enabled the macros for a workbook

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1