VBA Script Columns to Text

[lefty38]

Hello
i am looking for a vb script that will convert "columns into text"

Criteria
at each course I need the ID to be placed into a comma separated text cell

function "transpose" only places the ID into separate columns --
this will not work

Like the desired output below

the one catch is the desired output can not be longer than 24 ID numbers wide



[TABLE="width: 258"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

<tbody>
[TD="class: xl66"] Course
[/TD]
[TD="class: xl66, width: 129"] ID [/TD]

[TD="class: xl67"] 33004 [/TD]
[TD="class: xl67"] 203212 [/TD]

[TD="class: xl67"] 75031 [/TD]
[TD="class: xl67"] 3075 [/TD]

[TD="class: xl67"] 75031 [/TD]
[TD="class: xl67"] 272278 [/TD]

[TD="class: xl67"] 75402 [/TD]
[TD="class: xl67"] 7265 [/TD]

[TD="class: xl67"] 75402 [/TD]
[TD="class: xl67"] 19701 [/TD]

[TD="class: xl67"] 75402 [/TD]
[TD="class: xl67"] 22150 [/TD]

[TD="class: xl67"] 75402 [/TD]
[TD="class: xl67"] 22949 [/TD]

[TD="class: xl67"] 75402 [/TD]
[TD="class: xl67"] 29850 [/TD]

[TD="class: xl67"] 75402 [/TD]
[TD="class: xl67"] 71350 [/TD]

[TD="class: xl67"] 75402 [/TD]
[TD="class: xl67"] 73784 [/TD]

[TD="class: xl67"] 75402 [/TD]
[TD="class: xl67"] 74850 [/TD]

[TD="class: xl67"] 75402 [/TD]
[TD="class: xl67"] 90643 [/TD]

[TD="class: xl67"] 75402 [/TD]
[TD="class: xl67"] 95914 [/TD]

[TD="class: xl67"] 75402 [/TD]
[TD="class: xl67"] 113259 [/TD]

[TD="class: xl67"] 75402 [/TD]
[TD="class: xl67"] 124119 [/TD]

[TD="class: xl67"] 75402 [/TD]
[TD="class: xl67"] 130399 [/TD]

[TD="class: xl67"] 75402 [/TD]
[TD="class: xl67"] 137030 [/TD]

[TD="class: xl67"] 75402 [/TD]
[TD="class: xl67"] 242445 [/TD]

[TD="class: xl67"] 75402 [/TD]
[TD="class: xl67"] 323047 [/TD]

[TD="class: xl67"] 75402 [/TD]
[TD="class: xl67"] 417657 [/TD]

[TD="class: xl67"] 75402 [/TD]
[TD="class: xl67"] 1087406 [/TD]

[TD="class: xl67"] 75402 [/TD]
[TD="class: xl67"] 1440149 [/TD]

[TD="class: xl67"] 75402 [/TD]
[TD="class: xl67"] 1771579 [/TD]

[TD="class: xl67"] 75402 [/TD]
[TD="class: xl67"] 2054268 [/TD]

[TD="class: xl67"] 75402 [/TD]
[TD="class: xl67"] 2194679 [/TD]

[TD="class: xl67"] 75402 [/TD]
[TD="class: xl65"] 1661707
[/TD]

[TD="class: xl67"] 75402 [/TD]
[TD="class: xl65"] 1731981
[/TD]

[TD="class: xl67"] 75402 [/TD]
[TD="class: xl65"] 1753502
[/TD]

[TD="class: xl65"] 75735 [/TD]
[TD="class: xl65"] 1781611
[/TD]

[TD="class: xl65"] 75735 [/TD]
[TD="class: xl65"] 1796959
[/TD]

[TD="class: xl65"] 75735 [/TD]
[TD="class: xl65"] 2130582
[/TD]

[TD="class: xl65"] 75735 [/TD]
[TD="class: xl65"] 2219557
[/TD]

</tbody>

when the target output reaches 24 id numbers (stop)
repeat with the same course and get the next set of numbers -- if reaches 24 ID then
repeat again
New Course - start again

desired output[TABLE="width: 586"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Course
[/TD]
[TD][/TD]
[TD]ID
[/TD]
[/TR]
[TR]
[TD="align: right"]33004[/TD]
[TD][/TD]
[TD]203212,[/TD]
[/TR]
[TR]
[TD="align: right"]75031
[/TD]
[TD][/TD]
[TD]3075,272278[/TD]
[/TR]
[TR]
[TD="align: right"]75402
[/TD]
[TD][/TD]
[TD]7265,19701,22150,29850,73784,74850,95914,113259,==> up to 24 ID numbers
[/TD]
[/TR]
[TR]
[TD="align: right"]75402[/TD]
[TD][/TD]
[TD]continue with remaining ID numbers 1731981,1753502,1771579,2054268,2194679[/TD]
[/TR]
[TR]
[TD="align: right"]75735[/TD]
[TD][/TD]
[TD]1781611,1796959,2130582,2219557
[/TD]
[/TR]
</tbody>[/TABLE]

and so on till the last row of data

the best situation -- would be putting each course and corresponding "text"
on the same row and on a new worksheet with the worksheet's name
the same as the course number

the next step in my process is to take the text and insert into another
program for further processing

and of course thank you in advance
[TABLE="width: 500"]
<tbody>[TR]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

 

[lefty38]

Fantastic Rick

the code worked perfectly!

thank you and all of the people who contributed