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

Phaleron

New Member
Joined
Aug 14, 2019
Messages
8
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.
 
Oh sorry - I randomized the data in both cases. It was just an example.

I didn't have time to mess with volume yet but it would follow the same layout as the other variables.

In this case the value in cell E3 would end up in L3 and E493 would end up in L4 as an example.
 
Upvote 0

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
You could make it easier for me.
You can send another file where the column values ​​match the result. Otherwise I do not understand.


I insist, maybe it's very obvious to you. But I have no idea what you want.
 
Upvote 0
Do not worry, take your time. When you have it with pleasure I check it.
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top