ThePianoman
New Member
- Joined
- Mar 15, 2011
- Messages
- 15
I'm looking for a bit of help for a pretty straightforward small project. I'm fairly familiar with all the basic functions of excel, VLOOKUP, pivot tables, etc., but clueless when it comes to VBA. I realize it can probably be done manually pretty easily, or using helper columns or similar, but I will need to perform the function several times and make it easy for other research study sites to use. So automated with VBA is the way I want to go. I'm willing to donate a small fee to whoever can work with me on this- you guys are the experts.
Here's a brief rundown:
[TABLE="width: 700"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88"]demo_mrn[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl65, width: 127"]ffi_shrinking_score[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl65, width: 146"]ffi_gripstrength_score[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD="class: xl65, width: 147"]ffi_slowwalking_score[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 138"]
<tbody>[TR]
[TD="class: xl65, width: 138"]ffi_lowactivity_score[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 74"]
<tbody>[TR]
[TD="class: xl65, width: 74"]ffi_fr[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81"]cfs_cfsscore[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD="class: xl65, width: 100"]rai_score_total[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]11111111[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]89[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]22222222[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]55[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]33333333[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]67[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]44444444[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]42[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]55555555[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]66666666[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]67[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]77777777[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]84[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]88888888[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]77[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 700"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88"]demo_mrn[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl65, width: 127"]ffi_shrinking_score[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl65, width: 146"]ffi_gripstrength_score[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD="class: xl65, width: 147"]ffi_slowwalking_score[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 138"]
<tbody>[TR]
[TD="class: xl65, width: 138"]ffi_lowactivity_score[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 74"]
<tbody>[TR]
[TD="class: xl65, width: 74"]ffi_fr[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81"]cfs_cfsscore[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD="class: xl65, width: 100"]rai_score_total[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]11111111[/TD]
[TD]#F4_ffi_shrinking_score_0[/TD]
[TD]#F4_ffi_gripstrength_score_1[/TD]
[TD]#F4_ffi_slowwalking_score_0[/TD]
[TD]#F4_ffi_lowactivity_score_0[/TD]
[TD]#F4_ffi_fr_1[/TD]
[TD]#F4_cfs_cfsscore_1[/TD]
[TD]#F4_rai_score_total_89[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]22222222[/TD]
[TD]#F4_ffi_shrinking_score_3[/TD]
[TD]#F4_ffi_gripstrength_score_2[/TD]
[TD]#F4_ffi_slowwalking_score_2[/TD]
[TD]#F4_ffi_lowactivity_score_1[/TD]
[TD]#F4_ffi_fr_3[/TD]
[TD]#F4_cfs_cfsscore_4[/TD]
[TD]#F4_rai_score_total_55[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]33333333[/TD]
[TD]#F4_ffi_shrinking_score_1[/TD]
[TD]#F4_ffi_gripstrength_score_2[/TD]
[TD]#F4_ffi_slowwalking_score_1[/TD]
[TD]#F4_ffi_lowactivity_score_1[/TD]
[TD]#F4_ffi_fr_0[/TD]
[TD]#F4_cfs_cfsscore_3[/TD]
[TD]#F4_rai_score_total_67[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]44444444[/TD]
[TD]#F4_ffi_shrinking_score_1[/TD]
[TD]#F4_ffi_gripstrength_score_1[/TD]
[TD]#F4_ffi_slowwalking_score_1[/TD]
[TD]#F4_ffi_lowactivity_score_1[/TD]
[TD]#F4_ffi_fr_4[/TD]
[TD]#F4_cfs_cfsscore_1[/TD]
[TD]#F4_rai_score_total_42[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]55555555[/TD]
[TD]#F4_ffi_shrinking_score_0[/TD]
[TD]#F4_ffi_gripstrength_score_0[/TD]
[TD]#F4_ffi_slowwalking_score_1[/TD]
[TD]#F4_ffi_lowactivity_score_2[/TD]
[TD]#F4_ffi_fr_2[/TD]
[TD]#F4_cfs_cfsscore_2[/TD]
[TD]#F4_rai_score_total_25[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]66666666[/TD]
[TD]#F4_ffi_shrinking_score_3[/TD]
[TD]#F4_ffi_gripstrength_score_1[/TD]
[TD]#F4_ffi_slowwalking_score_2[/TD]
[TD]#F4_ffi_lowactivity_score_1[/TD]
[TD]#F4_ffi_fr_3[/TD]
[TD]#F4_cfs_cfsscore_4[/TD]
[TD]#F4_rai_score_total_67[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]77777777[/TD]
[TD]#F4_ffi_shrinking_score_4[/TD]
[TD]#F4_ffi_gripstrength_score_1[/TD]
[TD]#F4_ffi_slowwalking_score_3[/TD]
[TD]#F4_ffi_lowactivity_score_3[/TD]
[TD]#F4_ffi_fr_2[/TD]
[TD]#F4_cfs_cfsscore_1[/TD]
[TD]#F4_rai_score_total_84[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]88888888[/TD]
[TD]#F4_ffi_shrinking_score_1[/TD]
[TD]#F4_ffi_gripstrength_score_4[/TD]
[TD]#F4_ffi_slowwalking_score_3[/TD]
[TD]#F4_ffi_lowactivity_score_2[/TD]
[TD]#F4_ffi_fr_2[/TD]
[TD]#F4_cfs_cfsscore_1[/TD]
[TD]#F4_rai_score_total_77[/TD]
[/TR]
</tbody>[/TABLE]
Thanks in advance for your help.
Here's a brief rundown:
- I will periodically export a dataset from a research study database, in excel format. It will look something like this (with exactly this many columns, but potentially many more rows of data):
[TABLE="width: 700"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88"]demo_mrn[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl65, width: 127"]ffi_shrinking_score[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl65, width: 146"]ffi_gripstrength_score[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD="class: xl65, width: 147"]ffi_slowwalking_score[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 138"]
<tbody>[TR]
[TD="class: xl65, width: 138"]ffi_lowactivity_score[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 74"]
<tbody>[TR]
[TD="class: xl65, width: 74"]ffi_fr[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81"]cfs_cfsscore[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD="class: xl65, width: 100"]rai_score_total[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]11111111[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]89[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]22222222[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]55[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]33333333[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]67[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]44444444[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]42[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]55555555[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]66666666[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]67[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]77777777[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]84[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]88888888[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]77[/TD]
[/TR]
</tbody>[/TABLE]
- The data (B2:H9) needs to have "#F4_[column header in row 1]_" appended to it so that it will look exactly like this:
[TABLE="width: 700"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88"]demo_mrn[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl65, width: 127"]ffi_shrinking_score[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl65, width: 146"]ffi_gripstrength_score[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD="class: xl65, width: 147"]ffi_slowwalking_score[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 138"]
<tbody>[TR]
[TD="class: xl65, width: 138"]ffi_lowactivity_score[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 74"]
<tbody>[TR]
[TD="class: xl65, width: 74"]ffi_fr[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81"]cfs_cfsscore[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD="class: xl65, width: 100"]rai_score_total[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]11111111[/TD]
[TD]#F4_ffi_shrinking_score_0[/TD]
[TD]#F4_ffi_gripstrength_score_1[/TD]
[TD]#F4_ffi_slowwalking_score_0[/TD]
[TD]#F4_ffi_lowactivity_score_0[/TD]
[TD]#F4_ffi_fr_1[/TD]
[TD]#F4_cfs_cfsscore_1[/TD]
[TD]#F4_rai_score_total_89[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]22222222[/TD]
[TD]#F4_ffi_shrinking_score_3[/TD]
[TD]#F4_ffi_gripstrength_score_2[/TD]
[TD]#F4_ffi_slowwalking_score_2[/TD]
[TD]#F4_ffi_lowactivity_score_1[/TD]
[TD]#F4_ffi_fr_3[/TD]
[TD]#F4_cfs_cfsscore_4[/TD]
[TD]#F4_rai_score_total_55[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]33333333[/TD]
[TD]#F4_ffi_shrinking_score_1[/TD]
[TD]#F4_ffi_gripstrength_score_2[/TD]
[TD]#F4_ffi_slowwalking_score_1[/TD]
[TD]#F4_ffi_lowactivity_score_1[/TD]
[TD]#F4_ffi_fr_0[/TD]
[TD]#F4_cfs_cfsscore_3[/TD]
[TD]#F4_rai_score_total_67[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]44444444[/TD]
[TD]#F4_ffi_shrinking_score_1[/TD]
[TD]#F4_ffi_gripstrength_score_1[/TD]
[TD]#F4_ffi_slowwalking_score_1[/TD]
[TD]#F4_ffi_lowactivity_score_1[/TD]
[TD]#F4_ffi_fr_4[/TD]
[TD]#F4_cfs_cfsscore_1[/TD]
[TD]#F4_rai_score_total_42[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]55555555[/TD]
[TD]#F4_ffi_shrinking_score_0[/TD]
[TD]#F4_ffi_gripstrength_score_0[/TD]
[TD]#F4_ffi_slowwalking_score_1[/TD]
[TD]#F4_ffi_lowactivity_score_2[/TD]
[TD]#F4_ffi_fr_2[/TD]
[TD]#F4_cfs_cfsscore_2[/TD]
[TD]#F4_rai_score_total_25[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]66666666[/TD]
[TD]#F4_ffi_shrinking_score_3[/TD]
[TD]#F4_ffi_gripstrength_score_1[/TD]
[TD]#F4_ffi_slowwalking_score_2[/TD]
[TD]#F4_ffi_lowactivity_score_1[/TD]
[TD]#F4_ffi_fr_3[/TD]
[TD]#F4_cfs_cfsscore_4[/TD]
[TD]#F4_rai_score_total_67[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]77777777[/TD]
[TD]#F4_ffi_shrinking_score_4[/TD]
[TD]#F4_ffi_gripstrength_score_1[/TD]
[TD]#F4_ffi_slowwalking_score_3[/TD]
[TD]#F4_ffi_lowactivity_score_3[/TD]
[TD]#F4_ffi_fr_2[/TD]
[TD]#F4_cfs_cfsscore_1[/TD]
[TD]#F4_rai_score_total_84[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]88888888[/TD]
[TD]#F4_ffi_shrinking_score_1[/TD]
[TD]#F4_ffi_gripstrength_score_4[/TD]
[TD]#F4_ffi_slowwalking_score_3[/TD]
[TD]#F4_ffi_lowactivity_score_2[/TD]
[TD]#F4_ffi_fr_2[/TD]
[TD]#F4_cfs_cfsscore_1[/TD]
[TD]#F4_rai_score_total_77[/TD]
[/TR]
</tbody>[/TABLE]
- The dataset could include up to 1500 rows. Preferably I would like the VBA function to be initiated with a button. So it would work like this:
- Export the data to excel.
- Copy/Paste data into the macro worksheet
- Click the button to convert/append the data
- Click another button to create a new workbook (regular excel file), and copy the converted data into it.
Thanks in advance for your help.