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

[Phaleron]

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.

 

[DanteAmor]

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.

 

[Phaleron]

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

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

 

[DanteAmor]

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.

 

[Phaleron]

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

 

[DanteAmor]

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.

 

[Phaleron]

Try the onedrive link: https://1drv.ms/x/s!Alha_kaJdn74vUSq3RRh4rbo3pCt?e=jig0x7

 

[DanteAmor]

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 ?

 

[Phaleron]

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.

 

[DanteAmor]

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)