Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: VBA Script for Copy/Transpose and combine cells - help requested
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Script for Copy/Transpose and combine cells - help requested

    Hi everyone,

    I'm completely new to VBA and scripts so I finally decided to make a post after trying unsuccessfully to parse through things here and stackoverflow over the last couple days.

    The problem is that the software I'm using spits out the obtained data in a completely unusable way for analysis and I'm trying to avoid a week of manual copy/paste/transposing now and for then every so often for rest of my life (potentially).

    Computer Setup:

    OS X 10.14.6
    Excel for Mac 16.16.12

    Excel File setup:

    Number of Rows: 14701
    Number of Columns: 7 (A:G)

    Excel File Setup:

    Row 1: |SUBJECT | VISIT | TYPE | AREA | MEAN | STDD | Vol |

    For SUBJECT: A2:A981 all = 1, the next 979 cells = 2, etc. to 15
    For VISIT = Base or After
    TYPE = 1,2,3,4,5,6,7,8 (8 possible variables here)
    AREA = 2 groupings of cells - first one is D2:D70, second is D71:D99 this pattern repeats for the whole spreadsheet. But each cell is a different Variable (area) I need to look at.
    MEAN, STTD, Vol = numbers

    What is instead of the current setup for it to look like this for each TYPE (so 8 separate sheets or all on one giant sheet is fine too):

    SUBJECT|VISIT_Base_Area_1_MEAN|VISIT_After_Area_1_MEAN|VISIT_Base_Area_1_Vol|VISIT_After_Area_1_Vol|

    *repeat for each area for the subject so that each area+mean and area+vol are organized that the base and after results are next to each other on the spreadsheet.
    Each subject needs to have all of their data on a single row only.

    I have gotten as far as writing a little script to copy/transpose the data into rows with me manually altering the code each time to avoid some mouse clicks and lots of scrolling but I haven't been able to get a loop to run.

    Big thanks to anyone who might be able to help.

    Also - if anyone knows of any reputable online excel courses worth taking I'd appreciate some feedback there as well. There are a tonne out there and I don't know how to tell the good ones apart from the rip-offs.

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,260
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA Script for Copy/Transpose and combine cells - help requested

    Welcome to the forum!

    It will help if you put a sample of how your data is and the expected result.

    You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
    Last edited by DanteAmor; Aug 17th, 2019 at 11:56 PM.
    Regards Dante Amor

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Script for Copy/Transpose and combine cells - help requested

    Link to Example data is here: https://www.dropbox.com/preview/Publ...?role=personal

    Apologies for the time it took to put it together. It's been a busy week and I appreciate your help.

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,260
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA Script for Copy/Transpose and combine cells - help requested

    My apologies too, I am reading your requirement, and you have Excel for Mac, I don't have that version, I can't help you, I hope that Mac experts can respond.
    Regards Dante Amor

  5. #5
    New Member
    Join Date
    Aug 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Script for Copy/Transpose and combine cells - help requested

    I also have access to Excel 2010 for PC if needed on a different machine.

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,260
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA Script for Copy/Transpose and combine cells - help requested

    Quote Originally Posted by Phaleron View Post
    I also have access to Excel 2010 for PC if needed on a different machine.
    In agreement.
    I tried to download the file, but it does not exist.
    You can upload it again in the PC version and share it.
    Regards Dante Amor

  7. #7
    New Member
    Join Date
    Aug 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Script for Copy/Transpose and combine cells - help requested


  8. #8
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,260
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA Script for Copy/Transpose and combine cells - help requested

    Ok, I already have access to your file.
    Now, you can explain the following:
    Maybe, it's obvious to you, but I don't understand how you got to these results, you could explain how to get the following results:

     IJKLM
    1NEEDED OUTPUT:    
    2subjectTypeA_All AREAs_ATypeA_All AREAs_CTypeA_AREA1_ATypeA_AREA1_C
    3AAAAA0.0944150.0869530.1002000.088931
    4BBBBB0.1024100.1043000.0946940.146600


    TypeA_All AREAs_A TypeA_All AREAs_C TypeA_AREA1_A TypeA_AREA1_C ?
    0.094415 0.086953 0.100200 0.088931 ?
    0.102410 0.104300 0.094694 0.146600 ?
    Regards Dante Amor

  9. #9
    New Member
    Join Date
    Aug 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Script for Copy/Transpose and combine cells - help requested

    Ah ok.

    Each Subject has two visits (A & C)
    Each Subject has multiple areas & multiple types
    Each Subject needs to have all of their data in a single row

    The way data is outputted from the program creates multiple rows for each participant
    I need to have data as follows:

    Subject: Type1_Area1_VisitA in one column and in the next column: Type1_Area1_VisitC

    So each type of scan is done together.
    Within that the areas from VisitA and VisitC need to be placed next to each other.

    I hope that is clear - let me know if it isn't. Thank you very much for your patience with me and your help.

  10. #10
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,260
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA Script for Copy/Transpose and combine cells - help requested

    No, I don't think I explained myself.
    I don't understand how you got this data: "TypeA_All AREAs_A" literally how you got this data "TypeA_All AREAs_A" "TypeA_All AREAs_C TypeA_AREA1_A TypeA_AREA1_C", etc,
    You also have to explain how you came to this data: 0.094415 . Since I can't find it on the sheet.

    0.086953 0.100200 0.088931 ?, etc.


    There is also no data in column "G" (Vol)
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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