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.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:12pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:198.65px;" /><col style="width:169.19px;" /><col style="width:169.19px;" /><col style="width:169.19px;" /><col style="width:169.19px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >NEEDED OUTPUT:</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >subject</td><td >TypeA_All AREAs_A</td><td >TypeA_All AREAs_C</td><td >TypeA_AREA1_A</td><td >TypeA_AREA1_C</td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >AAAAA</td><td style="text-align:right; ">0.094415</td><td style="text-align:right; ">0.086953</td><td style="text-align:right; ">0.100200</td><td style="text-align:right; ">0.088931</td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >BBBBB</td><td style="text-align:right; ">0.102410</td><td style="text-align:right; ">0.104300</td><td style="text-align:right; ">0.094694</td><td style="text-align:right; ">0.146600</td></tr></table>

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 ?
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

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