VBA code to move multiple values associated with one student to specific cell

J

JLGUZMAN

New Member
Joined
Jun 15, 2018
Messages
8
Hello all,
So I am just starting to venture into Excel VBA and I have been able to patch some things together with the help of this site, but I am now stumped as to how to resolve this one issue. I have a large report that has the workshop segments for a specific student registered that semester listed under their registration information and I need it to be associated with that students record for that specific semester and course. There is also the added issue that it is coming up with duplicates for each of the workshops for that one student, and there is not rhyme or reason as to how many times it duplicates it. I have over 500 students each semester and need to get these reports out to instructors on a timely basis at the start of the semester.

[TABLE="width: 700"]
<tbody>[TR]
[TD="class: xl63, width: 180, bgcolor: gray"]Site ID
[/TD]
[TD="class: xl63, width: 65, bgcolor: gray"]Status
[/TD]
[TD="class: xl63, width: 60, bgcolor: gray"]Report Month
[/TD]
[TD="class: xl63, width: 94, bgcolor: gray"]Type Of Form
[/TD]
[TD="class: xl75, width: 94, bgcolor: gray"]Training Date
[/TD]
[TD="class: xl63, width: 93, bgcolor: gray"]Course ID
[/TD]
[TD="class: xl63, width: 103, bgcolor: gray"]Student ID
[/TD]
[TD="class: xl63, width: 242, bgcolor: gray"]Workshops:
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 180, bgcolor: #E6F3E4"]SF08891
[/TD]
[TD="class: xl66, width: 65, bgcolor: #E6F3E4"]Rejected
[/TD]
[TD="class: xl66, width: 60, bgcolor: #E6F3E4"]May
[/TD]
[TD="class: xl66, width: 94, bgcolor: #E6F3E4"]Reg
[/TD]
[TD="class: xl76, width: 94, bgcolor: #E6F3E4"]4/15/2018
[/TD]
[TD="class: xl66, width: 93, bgcolor: #E6F3E4"]S088456
[/TD]
[TD="class: xl66, width: 103, bgcolor: #E6F3E4"]W008546
[/TD]
[TD="class: xl83, width: 242, bgcolor: yellow, align: left"]Need workshops that correspond to this student to be moved into Cell H3 without duplications.
[/TD]
[/TR]
[TR]
[TD="class: xl74, width: 180, bgcolor: transparent"](013) Harm Reduction
[/TD]
[TD="class: xl67, width: 65, bgcolor: transparent"][/TD]
[TD="class: xl67, width: 60, bgcolor: transparent"][/TD]
[TD="class: xl67, width: 94, bgcolor: transparent"][/TD]
[TD="class: xl77, width: 94, bgcolor: transparent"][/TD]
[TD="class: xl68, width: 93, bgcolor: transparent"][/TD]
[TD="class: xl68, width: 103, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, width: 180, bgcolor: transparent"](013) Harm Reduction
[/TD]
[TD="class: xl70, width: 65, bgcolor: transparent"][/TD]
[TD="class: xl70, width: 60, bgcolor: transparent"][/TD]
[TD="class: xl70, width: 94, bgcolor: transparent"][/TD]
[TD="class: xl78, width: 94, bgcolor: transparent"][/TD]
[TD="class: xl70, width: 93, bgcolor: transparent"][/TD]
[TD="class: xl70, width: 103, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, width: 180, bgcolor: transparent"](013) Harm Reduction
[/TD]
[TD="class: xl71, width: 65, bgcolor: transparent"][/TD]
[TD="class: xl71, width: 60, bgcolor: transparent"][/TD]
[TD="class: xl71, width: 94, bgcolor: transparent"][/TD]
[TD="class: xl79, width: 94, bgcolor: transparent"][/TD]
[TD="class: xl70, width: 93, bgcolor: transparent"][/TD]
[TD="class: xl70, width: 103, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, width: 180, bgcolor: transparent"](201) Writing Skills
[/TD]
[TD="class: xl70, width: 65, bgcolor: transparent"][/TD]
[TD="class: xl70, width: 60, bgcolor: transparent"][/TD]
[TD="class: xl70, width: 94, bgcolor: transparent"][/TD]
[TD="class: xl78, width: 94, bgcolor: transparent"][/TD]
[TD="class: xl70, width: 93, bgcolor: transparent"][/TD]
[TD="class: xl70, width: 103, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, width: 180, bgcolor: transparent"](201) Writing Skills
[/TD]
[TD="class: xl70, width: 65, bgcolor: transparent"][/TD]
[TD="class: xl70, width: 60, bgcolor: transparent"][/TD]
[TD="class: xl70, width: 94, bgcolor: transparent"][/TD]
[TD="class: xl78, width: 94, bgcolor: transparent"][/TD]
[TD="class: xl70, width: 93, bgcolor: transparent"][/TD]
[TD="class: xl70, width: 103, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, width: 180, bgcolor: transparent"](201) Writing Skills
[/TD]
[TD="class: xl73, width: 65, bgcolor: transparent"][/TD]
[TD="class: xl73, width: 60, bgcolor: transparent"][/TD]
[TD="class: xl73, width: 94, bgcolor: transparent"][/TD]
[TD="class: xl80, width: 94, bgcolor: transparent"][/TD]
[TD="class: xl73, width: 93, bgcolor: transparent"][/TD]
[TD="class: xl73, width: 103, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 180, bgcolor: #E6F3E4"]SF08564
[/TD]
[TD="class: xl66, width: 65, bgcolor: #E6F3E4"]Rejected
[/TD]
[TD="class: xl66, width: 60, bgcolor: #E6F3E4"]May
[/TD]
[TD="class: xl66, width: 94, bgcolor: #E6F3E4"]Reg
[/TD]
[TD="class: xl76, width: 94, bgcolor: #E6F3E4"]4/15/2018
[/TD]
[TD="class: xl66, width: 93, bgcolor: #E6F3E4"]S054685
[/TD]
[TD="class: xl66, width: 103, bgcolor: #E6F3E4"]W004685
[/TD]
[TD="class: xl83, width: 242, bgcolor: yellow, align: left"]Need workshops that correspond to this student to be moved into Cell H9 without duplications.
[/TD]
[/TR]
[TR]
[TD="class: xl74, width: 180, bgcolor: transparent"](013) Harm Reduction
[/TD]
[TD="class: xl68, width: 65, bgcolor: transparent"][/TD]
[TD="class: xl68, width: 60, bgcolor: transparent"][/TD]
[TD="class: xl68, width: 94, bgcolor: transparent"][/TD]
[TD="class: xl81, width: 94, bgcolor: transparent"][/TD]
[TD="class: xl68, width: 93, bgcolor: transparent"][/TD]
[TD="class: xl68, width: 103, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, width: 180, bgcolor: transparent"](013) Harm Reduction
[/TD]
[TD="class: xl70, width: 65, bgcolor: transparent"][/TD]
[TD="class: xl70, width: 60, bgcolor: transparent"][/TD]
[TD="class: xl70, width: 94, bgcolor: transparent"][/TD]
[TD="class: xl78, width: 94, bgcolor: transparent"][/TD]
[TD="class: xl70, width: 93, bgcolor: transparent"][/TD]
[TD="class: xl70, width: 103, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, width: 180, bgcolor: transparent"](201) Writing Skills
[/TD]
[TD="class: xl70, width: 65, bgcolor: transparent"][/TD]
[TD="class: xl70, width: 60, bgcolor: transparent"][/TD]
[TD="class: xl70, width: 94, bgcolor: transparent"][/TD]
[TD="class: xl78, width: 94, bgcolor: transparent"][/TD]
[TD="class: xl70, width: 93, bgcolor: transparent"][/TD]
[TD="class: xl70, width: 103, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, width: 180, bgcolor: transparent"](201) Writing Skills
[/TD]
[TD="class: xl70, width: 65, bgcolor: transparent"][/TD]
[TD="class: xl70, width: 60, bgcolor: transparent"][/TD]
[TD="class: xl70, width: 94, bgcolor: transparent"][/TD]
[TD="class: xl78, width: 94, bgcolor: transparent"][/TD]
[TD="class: xl70, width: 93, bgcolor: transparent"][/TD]
[TD="class: xl70, width: 103, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, width: 180, bgcolor: transparent"](201) Writing Skills
[/TD]
[TD="class: xl70, width: 65, bgcolor: transparent"][/TD]
[TD="class: xl70, width: 60, bgcolor: transparent"][/TD]
[TD="class: xl70, width: 94, bgcolor: transparent"][/TD]
[TD="class: xl78, width: 94, bgcolor: transparent"][/TD]
[TD="class: xl70, width: 93, bgcolor: transparent"][/TD]
[TD="class: xl70, width: 103, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, width: 180, bgcolor: transparent"](201) Writing Skills
[/TD]
[TD="class: xl73, width: 65, bgcolor: transparent"][/TD]
[TD="class: xl73, width: 60, bgcolor: transparent"][/TD]
[TD="class: xl73, width: 94, bgcolor: transparent"][/TD]
[TD="class: xl80, width: 94, bgcolor: transparent"][/TD]
[TD="class: xl73, width: 93, bgcolor: transparent"][/TD]
[TD="class: xl73, width: 103, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
Hi JLGUZMAN,

Welcome to the MrExcel board.

If what you have posted is the before (I have no idea where cell H3 is), then you might want to post the after. Meaning if we could see what your expected result is (with column and row labels), it would make this a lot easier...
 
Last edited:
Upvote 0
Hello,
Sorry about that. I am new to this whole bloging for help thing.

Here is the image of what I am looking for. The top is the before and the bottom is after. Thank you.

[TABLE="width: 648"]
<colgroup><col width="64" style="width: 48pt;"> <col width="100" style="width: 75pt; mso-width-source: userset; mso-width-alt: 3657;"> <col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;"> <col width="57" style="width: 43pt; mso-width-source: userset; mso-width-alt: 2084;"> <col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;"> <col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;"> <col width="92" style="width: 69pt; mso-width-source: userset; mso-width-alt: 3364;"> <col width="97" style="width: 73pt; mso-width-source: userset; mso-width-alt: 3547;"> <col width="235" style="width: 176pt; mso-width-source: userset; mso-width-alt: 8594;"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 100, bgcolor: transparent"]A[/TD]
[TD="class: xl64, width: 71, bgcolor: transparent"]B[/TD]
[TD="class: xl64, width: 57, bgcolor: transparent"]C[/TD]
[TD="class: xl64, width: 68, bgcolor: transparent"]D[/TD]
[TD="class: xl64, width: 80, bgcolor: transparent"]E[/TD]
[TD="class: xl64, width: 92, bgcolor: transparent"]F[/TD]
[TD="class: xl64, width: 97, bgcolor: transparent"]G[/TD]
[TD="class: xl64, width: 235, bgcolor: transparent"]H[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]1[/TD]
[TD="class: xl63, width: 100, bgcolor: gray"]Site ID[/TD]
[TD="class: xl63, width: 71, bgcolor: gray"]Status[/TD]
[TD="class: xl63, width: 57, bgcolor: gray"]Month[/TD]
[TD="class: xl63, width: 68, bgcolor: gray"]Type Of Form[/TD]
[TD="class: xl63, width: 80, bgcolor: gray"]Training Date[/TD]
[TD="class: xl63, width: 92, bgcolor: gray"]Course ID[/TD]
[TD="class: xl63, width: 97, bgcolor: gray"]Student ID[/TD]
[TD="class: xl63, width: 235, bgcolor: gray"]Workshops:[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]2[/TD]
[TD="class: xl66, width: 100, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6F3E4]#E6F3E4[/URL] "]SF08891[/TD]
[TD="class: xl66, width: 71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6F3E4]#E6F3E4[/URL] "]Rejected[/TD]
[TD="class: xl66, width: 57, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6F3E4]#E6F3E4[/URL] "]May[/TD]
[TD="class: xl66, width: 68, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6F3E4]#E6F3E4[/URL] "]Reg[/TD]
[TD="class: xl67, width: 80, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6F3E4]#E6F3E4[/URL] , align: right"]4/15/2018[/TD]
[TD="class: xl66, width: 92, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6F3E4]#E6F3E4[/URL] "]S088456[/TD]
[TD="class: xl66, width: 97, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6F3E4]#E6F3E4[/URL] "]W008546[/TD]
[TD="class: xl68, width: 235, bgcolor: yellow"]Need workshops that correspond to this student to be moved into Cell H3 without duplications.[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]3[/TD]
[TD="class: xl69, width: 100, bgcolor: white"](013) Harm Reduction[/TD]
[TD="class: xl65, width: 71, bgcolor: white"] [/TD]
[TD="class: xl65, width: 57, bgcolor: white"] [/TD]
[TD="class: xl65, width: 68, bgcolor: white"] [/TD]
[TD="class: xl65, width: 80, bgcolor: white"] [/TD]
[TD="class: xl65, width: 92, bgcolor: white"] [/TD]
[TD="class: xl65, width: 97, bgcolor: white"] [/TD]
[TD="class: xl65, width: 235, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]4[/TD]
[TD="class: xl69, width: 100, bgcolor: white"](013) Harm Reduction[/TD]
[TD="class: xl65, width: 71, bgcolor: white"] [/TD]
[TD="class: xl65, width: 57, bgcolor: white"] [/TD]
[TD="class: xl65, width: 68, bgcolor: white"] [/TD]
[TD="class: xl65, width: 80, bgcolor: white"] [/TD]
[TD="class: xl65, width: 92, bgcolor: white"] [/TD]
[TD="class: xl65, width: 97, bgcolor: white"] [/TD]
[TD="class: xl65, width: 235, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]5[/TD]
[TD="class: xl69, width: 100, bgcolor: white"](013) Harm Reduction[/TD]
[TD="class: xl65, width: 71, bgcolor: white"] [/TD]
[TD="class: xl65, width: 57, bgcolor: white"] [/TD]
[TD="class: xl65, width: 68, bgcolor: white"] [/TD]
[TD="class: xl65, width: 80, bgcolor: white"] [/TD]
[TD="class: xl65, width: 92, bgcolor: white"] [/TD]
[TD="class: xl65, width: 97, bgcolor: white"] [/TD]
[TD="class: xl65, width: 235, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]6[/TD]
[TD="class: xl69, width: 100, bgcolor: white"](201) Writing Skills[/TD]
[TD="class: xl65, width: 71, bgcolor: white"] [/TD]
[TD="class: xl65, width: 57, bgcolor: white"] [/TD]
[TD="class: xl65, width: 68, bgcolor: white"] [/TD]
[TD="class: xl65, width: 80, bgcolor: white"] [/TD]
[TD="class: xl65, width: 92, bgcolor: white"] [/TD]
[TD="class: xl65, width: 97, bgcolor: white"] [/TD]
[TD="class: xl65, width: 235, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]7[/TD]
[TD="class: xl69, width: 100, bgcolor: white"](201) Writing Skills[/TD]
[TD="class: xl65, width: 71, bgcolor: white"] [/TD]
[TD="class: xl65, width: 57, bgcolor: white"] [/TD]
[TD="class: xl65, width: 68, bgcolor: white"] [/TD]
[TD="class: xl65, width: 80, bgcolor: white"] [/TD]
[TD="class: xl65, width: 92, bgcolor: white"] [/TD]
[TD="class: xl65, width: 97, bgcolor: white"] [/TD]
[TD="class: xl65, width: 235, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]8[/TD]
[TD="class: xl69, width: 100, bgcolor: white"](201) Writing Skills[/TD]
[TD="class: xl65, width: 71, bgcolor: white"] [/TD]
[TD="class: xl65, width: 57, bgcolor: white"] [/TD]
[TD="class: xl65, width: 68, bgcolor: white"] [/TD]
[TD="class: xl65, width: 80, bgcolor: white"] [/TD]
[TD="class: xl65, width: 92, bgcolor: white"] [/TD]
[TD="class: xl65, width: 97, bgcolor: white"] [/TD]
[TD="class: xl65, width: 235, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]9[/TD]
[TD="class: xl66, width: 100, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6F3E4]#E6F3E4[/URL] "]SF08564[/TD]
[TD="class: xl66, width: 71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6F3E4]#E6F3E4[/URL] "]Rejected[/TD]
[TD="class: xl66, width: 57, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6F3E4]#E6F3E4[/URL] "]May[/TD]
[TD="class: xl66, width: 68, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6F3E4]#E6F3E4[/URL] "]Reg[/TD]
[TD="class: xl67, width: 80, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6F3E4]#E6F3E4[/URL] , align: right"]4/15/2018[/TD]
[TD="class: xl66, width: 92, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6F3E4]#E6F3E4[/URL] "]S054685[/TD]
[TD="class: xl66, width: 97, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6F3E4]#E6F3E4[/URL] "]W004685[/TD]
[TD="class: xl68, width: 235, bgcolor: yellow"]Need workshops that correspond to this student to be moved into Cell H9 without duplications.[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]10[/TD]
[TD="class: xl69, width: 100, bgcolor: white"](013) Harm Reduction[/TD]
[TD="class: xl65, width: 71, bgcolor: white"] [/TD]
[TD="class: xl65, width: 57, bgcolor: white"] [/TD]
[TD="class: xl65, width: 68, bgcolor: white"] [/TD]
[TD="class: xl65, width: 80, bgcolor: white"] [/TD]
[TD="class: xl65, width: 92, bgcolor: white"] [/TD]
[TD="class: xl65, width: 97, bgcolor: white"] [/TD]
[TD="class: xl65, width: 235, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]11[/TD]
[TD="class: xl69, width: 100, bgcolor: white"](013) Harm Reduction[/TD]
[TD="class: xl65, width: 71, bgcolor: white"] [/TD]
[TD="class: xl65, width: 57, bgcolor: white"] [/TD]
[TD="class: xl65, width: 68, bgcolor: white"] [/TD]
[TD="class: xl65, width: 80, bgcolor: white"] [/TD]
[TD="class: xl65, width: 92, bgcolor: white"] [/TD]
[TD="class: xl65, width: 97, bgcolor: white"] [/TD]
[TD="class: xl65, width: 235, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]12[/TD]
[TD="class: xl69, width: 100, bgcolor: white"](201) Writing Skills[/TD]
[TD="class: xl65, width: 71, bgcolor: white"] [/TD]
[TD="class: xl65, width: 57, bgcolor: white"] [/TD]
[TD="class: xl65, width: 68, bgcolor: white"] [/TD]
[TD="class: xl65, width: 80, bgcolor: white"] [/TD]
[TD="class: xl65, width: 92, bgcolor: white"] [/TD]
[TD="class: xl65, width: 97, bgcolor: white"] [/TD]
[TD="class: xl65, width: 235, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]13[/TD]
[TD="class: xl69, width: 100, bgcolor: white"](201) Writing Skills[/TD]
[TD="class: xl65, width: 71, bgcolor: white"] [/TD]
[TD="class: xl65, width: 57, bgcolor: white"] [/TD]
[TD="class: xl65, width: 68, bgcolor: white"] [/TD]
[TD="class: xl65, width: 80, bgcolor: white"] [/TD]
[TD="class: xl65, width: 92, bgcolor: white"] [/TD]
[TD="class: xl65, width: 97, bgcolor: white"] [/TD]
[TD="class: xl65, width: 235, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]14[/TD]
[TD="class: xl69, width: 100, bgcolor: white"](201) Writing Skills[/TD]
[TD="class: xl65, width: 71, bgcolor: white"] [/TD]
[TD="class: xl65, width: 57, bgcolor: white"] [/TD]
[TD="class: xl65, width: 68, bgcolor: white"] [/TD]
[TD="class: xl65, width: 80, bgcolor: white"] [/TD]
[TD="class: xl65, width: 92, bgcolor: white"] [/TD]
[TD="class: xl65, width: 97, bgcolor: white"] [/TD]
[TD="class: xl65, width: 235, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]15[/TD]
[TD="class: xl69, width: 100, bgcolor: white"](201) Writing Skills[/TD]
[TD="class: xl65, width: 71, bgcolor: white"] [/TD]
[TD="class: xl65, width: 57, bgcolor: white"] [/TD]
[TD="class: xl65, width: 68, bgcolor: white"] [/TD]
[TD="class: xl65, width: 80, bgcolor: white"] [/TD]
[TD="class: xl65, width: 92, bgcolor: white"] [/TD]
[TD="class: xl65, width: 97, bgcolor: white"] [/TD]
[TD="class: xl70, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"]A[/TD]
[TD="class: xl64, bgcolor: transparent"]B[/TD]
[TD="class: xl64, bgcolor: transparent"]C[/TD]
[TD="class: xl64, bgcolor: transparent"]D[/TD]
[TD="class: xl64, bgcolor: transparent"]E[/TD]
[TD="class: xl64, bgcolor: transparent"]F[/TD]
[TD="class: xl64, bgcolor: transparent"]G[/TD]
[TD="class: xl64, bgcolor: transparent"]H[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]1[/TD]
[TD="class: xl63, width: 100, bgcolor: gray"]Site ID[/TD]
[TD="class: xl63, width: 71, bgcolor: gray"]Status[/TD]
[TD="class: xl63, width: 57, bgcolor: gray"]Month[/TD]
[TD="class: xl63, width: 68, bgcolor: gray"]Type Of Form[/TD]
[TD="class: xl63, width: 80, bgcolor: gray"]Training Date[/TD]
[TD="class: xl63, width: 92, bgcolor: gray"]Course ID[/TD]
[TD="class: xl63, width: 97, bgcolor: gray"]Student ID[/TD]
[TD="class: xl63, width: 235, bgcolor: gray"]Workshops:[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]2[/TD]
[TD="class: xl66, width: 100, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6F3E4]#E6F3E4[/URL] "]SF08891[/TD]
[TD="class: xl66, width: 71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6F3E4]#E6F3E4[/URL] "]Rejected[/TD]
[TD="class: xl66, width: 57, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6F3E4]#E6F3E4[/URL] "]May[/TD]
[TD="class: xl66, width: 68, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6F3E4]#E6F3E4[/URL] "]Reg[/TD]
[TD="class: xl67, width: 80, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6F3E4]#E6F3E4[/URL] , align: right"]4/15/2018[/TD]
[TD="class: xl66, width: 92, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6F3E4]#E6F3E4[/URL] "]S088456[/TD]
[TD="class: xl66, width: 97, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6F3E4]#E6F3E4[/URL] "]W008546[/TD]
[TD="class: xl68, width: 235, bgcolor: yellow"](013) Harm Reduction, (201) Writing Skills[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]3[/TD]
[TD="class: xl66, width: 100, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6F3E4]#E6F3E4[/URL] "]SF08564[/TD]
[TD="class: xl66, width: 71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6F3E4]#E6F3E4[/URL] "]Rejected[/TD]
[TD="class: xl66, width: 57, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6F3E4]#E6F3E4[/URL] "]May[/TD]
[TD="class: xl66, width: 68, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6F3E4]#E6F3E4[/URL] "]Reg[/TD]
[TD="class: xl67, width: 80, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6F3E4]#E6F3E4[/URL] , align: right"]4/15/2018[/TD]
[TD="class: xl66, width: 92, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6F3E4]#E6F3E4[/URL] "]S054685[/TD]
[TD="class: xl66, width: 97, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6F3E4]#E6F3E4[/URL] "]W004685[/TD]
[TD="class: xl68, width: 235, bgcolor: yellow"](013) Harm Reduction, (201) Writing Skills[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Welcome to the MrExcel board!

Try this in a copy of your workbook.

Code: 
Sub Workshops()
  Dim rA As Range, c As Range
  Dim d As Object
  Dim lr As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  lr = Range("A" & Rows.Count).End(xlUp).Row
  Application.ScreenUpdating = False
  For Each rA In Range("G2:G" & lr).SpecialCells(xlBlanks).Areas
    d.RemoveAll
    For Each c In Intersect(rA.EntireRow, Columns("A"))
      d(c.Value) = Empty
    Next c
    rA.Cells(0, 2).Value = Join(d.Keys, ", ")
  Next rA
  Range("H2:H" & lr).SpecialCells(xlBlanks).EntireRow.Delete
  Application.ScreenUpdating = True
End Sub
 
Upvote 0
Thank you so much. This worked great. I do have one question as I am still learning VBA. If I tried changing the reference column to F by changing "For Each rA In Range("F2:F" & lr).SpecialCells(xlBlanks).Areas" but now it runs and deletes everything in the workbook. Course ID's are also unique and do not duplicate. Am I adjusting the code incorrectly? Thanks again.
 
Upvote 0
JLGUZMAN said:
Thank you so much. This worked great.
Click to expand...
You're welcome, that's great!


JLGUZMAN said:
I do have one question as I am still learning VBA. If I tried changing the reference column to F by changing "For Each rA In Range("F2:F" & lr).SpecialCells(xlBlanks).Areas" but now it runs and deletes everything in the workbook. Course ID's are also unique and do not duplicate. Am I adjusting the code incorrectly? Thanks again.
Click to expand...
The reason that everything (except row 1) was deleted after your change stems from the unchanged line
Rich (BB code): 
rA.Cells(0, 2).Value = Join(d.Keys, ", ")
This line writes the workshop data 1 column to the right of the referenced column you changed. That is, it would have over-written the student ID's and then when it came to the line ..
Rich (BB code): 
Range("H2:H" & lr).SpecialCells(xlBlanks).EntireRow.Delete
.. all cells would still be blank, and hence deleted.
Try changing the column ref from G to F, but also change that red 2 to 3 and see how it goes.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,815
Messages
6,181,136
Members
453,021
Latest member
Justyna P

Share this page

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