VBA Script Columns to Text

L

lefty38

Board Regular
Joined
Oct 27, 2005
Messages
85
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]
 
Sort by date Sort by votes
Ombir said:
Mick,

There seems to be some problem with this method as its not grouping correctly for below data set.

AB
CourseID

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]33004[/TD]
[TD="align: right"]203212[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]75031[/TD]
[TD="align: right"]3075[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]75031[/TD]
[TD="align: right"]272278[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]7265[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]19701[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]22150[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]22949[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]29850[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]71350[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]73784[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]74850[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]90643[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]95914[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]113259[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]124119[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]130399[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]137030[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]242445[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]323047[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]417657[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]1087406[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]1440149[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]1771579[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]2054268[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]2194679[/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]1661707[/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]1731981[/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]1753502[/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]75735[/TD]
[TD="align: right"]1781611[/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]75735[/TD]
[TD="align: right"]1796959[/TD]

[TD="align: center"]32[/TD]
[TD="align: right"]75735[/TD]
[TD="align: right"]2130582[/TD]

[TD="align: center"]33[/TD]
[TD="align: right"]75735[/TD]
[TD="align: right"]2219557[/TD]

[TD="align: center"]34[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]7265[/TD]

[TD="align: center"]35[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]19701[/TD]

[TD="align: center"]36[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]22150[/TD]

[TD="align: center"]37[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]22949[/TD]

[TD="align: center"]38[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]29850[/TD]

[TD="align: center"]39[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]71350[/TD]

[TD="align: center"]40[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]73784[/TD]

[TD="align: center"]41[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]74850[/TD]

[TD="align: center"]42[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]90643[/TD]

[TD="align: center"]43[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]95914[/TD]

[TD="align: center"]44[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]113259[/TD]

[TD="align: center"]45[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]124119[/TD]

[TD="align: center"]46[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]130399[/TD]

[TD="align: center"]47[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]137030[/TD]

[TD="align: center"]48[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]242445[/TD]

[TD="align: center"]49[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]323047[/TD]

[TD="align: center"]50[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]417657[/TD]

[TD="align: center"]51[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]1087406[/TD]

[TD="align: center"]52[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]1440149[/TD]

[TD="align: center"]53[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]1771579[/TD]

[TD="align: center"]54[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]2054268[/TD]

[TD="align: center"]55[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]2194679[/TD]

[TD="align: center"]56[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]1661707[/TD]

[TD="align: center"]57[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]1731981[/TD]

[TD="align: center"]58[/TD]
[TD="align: right"]75402[/TD]
[TD="align: right"]1753502[/TD]

[TD="align: center"]59[/TD]
[TD="align: right"]75031[/TD]
[TD="align: right"]7265[/TD]

[TD="align: center"]60[/TD]
[TD="align: right"]75031[/TD]
[TD="align: right"]19701[/TD]

[TD="align: center"]61[/TD]
[TD="align: right"]75031[/TD]
[TD="align: right"]22150[/TD]

[TD="align: center"]62[/TD]
[TD="align: right"]75031[/TD]
[TD="align: right"]22949[/TD]

[TD="align: center"]63[/TD]
[TD="align: right"]75031[/TD]
[TD="align: right"]29850[/TD]

[TD="align: center"]64[/TD]
[TD="align: right"]75031[/TD]
[TD="align: right"]71350[/TD]

[TD="align: center"]65[/TD]
[TD="align: right"]75031[/TD]
[TD="align: right"]73784[/TD]

[TD="align: center"]66[/TD]
[TD="align: right"]75031[/TD]
[TD="align: right"]74850[/TD]

[TD="align: center"]67[/TD]
[TD="align: right"]75031[/TD]
[TD="align: right"]90643[/TD]

[TD="align: center"]68[/TD]
[TD="align: right"]75031[/TD]
[TD="align: right"]95914[/TD]

[TD="align: center"]69[/TD]
[TD="align: right"]75031[/TD]
[TD="align: right"]113259[/TD]

[TD="align: center"]70[/TD]
[TD="align: right"]75031[/TD]
[TD="align: right"]124119[/TD]

[TD="align: center"]71[/TD]
[TD="align: right"]75031[/TD]
[TD="align: right"]130399[/TD]

[TD="align: center"]72[/TD]
[TD="align: right"]75031[/TD]
[TD="align: right"]137030[/TD]

[TD="align: center"]73[/TD]
[TD="align: right"]75031[/TD]
[TD="align: right"]242445[/TD]

[TD="align: center"]74[/TD]
[TD="align: right"]75031[/TD]
[TD="align: right"]323047[/TD]

[TD="align: center"]75[/TD]
[TD="align: right"]75031[/TD]
[TD="align: right"]417657[/TD]

[TD="align: center"]76[/TD]
[TD="align: right"]75031[/TD]
[TD="align: right"]1087406[/TD]

[TD="align: center"]77[/TD]
[TD="align: right"]75031[/TD]
[TD="align: right"]1440149[/TD]

[TD="align: center"]78[/TD]
[TD="align: right"]75031[/TD]
[TD="align: right"]1771579[/TD]

[TD="align: center"]79[/TD]
[TD="align: right"]75031[/TD]
[TD="align: right"]2054268[/TD]

[TD="align: center"]80[/TD]
[TD="align: right"]75031[/TD]
[TD="align: right"]2194679[/TD]

[TD="align: center"]81[/TD]
[TD="align: right"]75031[/TD]
[TD="align: right"]1661707[/TD]

[TD="align: center"]82[/TD]
[TD="align: right"]75031[/TD]
[TD="align: right"]1731981[/TD]

[TD="align: center"]83[/TD]
[TD="align: right"]75031[/TD]
[TD="align: right"]1753502[/TD]

</tbody>
Sheet3​
Click to expand...
What should the output be? Are we supposed to combined the split apart 75402 course into a single contiguous run of IDs? Same question about the 75031 course.
 
Last edited:
Upvote 0

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Rick Rothstein said:
What should the output be? Are we supposed to combined the split apart 75402 course into a single contiguous run of IDs? Same question about the 75031 course.
Click to expand...


Hi Rick,

Just found that MickG second alternative is also giving the correct output but data should be sorted on Course column.

OP wants to group the course with a maximum set of 24 IDs. Below is the required output for above sample data.

DE
CourseID
3075, 272278, 7265, 19701, 22150, 22949, 29850, 71350, 73784, 74850, 90643, 95914, 113259, 124119, 130399, 137030, 242445, 323047, 417657, 1087406, 1440149, 1771579, 2054268, 2194679
1661707, 1731981, 1753502
7265, 19701, 22150, 22949, 29850, 71350, 73784, 74850, 90643, 95914, 113259, 124119, 130399, 137030, 242445, 323047, 417657, 1087406, 1440149, 1771579, 2054268, 2194679, 1661707, 1731981
1753502, 7265, 19701, 22150, 22949, 29850, 71350, 73784, 74850, 90643, 95914, 113259, 124119, 130399, 137030, 242445, 323047, 417657, 1087406, 1440149, 1771579, 2054268, 2194679, 1661707
1731981, 1753502
1781611, 1796959, 2130582, 2219557

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]33004[/TD]
[TD="align: right"]203212[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]75031[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]75031[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]75402[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]75402[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]75402[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]75735[/TD]

</tbody>
Sheet4​
 
Upvote 0
Ombir said:
Hi Rick,

OP wants to group the course with a maximum set of 24 IDs. Below is the required output for above sample data.
Click to expand...
Are you sure the OP's data is split like you show? His original message seems to show the data already sorted (which he can do quite quickly if desired). Assuming the data is already sorted, here is the code I came up with (output to Columns D and E on same sheet)...
Code: 
Sub CoursesAndIDs()
  Dim R As Long, X As Long, Cnt As Long, Current As Long
  Dim Data As Variant, Result As Variant
  Data = Range("A2:B2").Resize(Cells(Rows.Count, "A").End(xlUp).Row - 1)
  ReDim Result(1 To UBound(Data), 1 To 2)
  For R = 1 To UBound(Data)
    If Data(R, 1) <> Current Then
      X = X + 1
      Cnt = 1
      Current = Data(R, 1)
      Result(X, 1) = Data(R, 1)
      Result(X, 2) = Data(R, 2)
    Else
      Cnt = Cnt + 1
      If Cnt > 24 Then
        X = X + 1
        Cnt = 1
        Result(X, 1) = Data(R, 1)
        Result(X, 2) = Data(R, 2)
      Else
        Result(X, 2) = Result(X, 2) & "," & Data(R, 2)
      End If
    End If
  Next
  Range("D1:E1") = Array("Course", "ID")
  Range("D2").Resize(UBound(Result), 2) = Result
End Sub
If the OP really does have split data like you show, it would be easy enough to add a line of code at the beginning of the macro to sort the original data first.
 
Upvote 0
Wow, this board really delivered again!
Ombir is correct on post #12
OP (me) requirement
is to have each course stop at up to 24 ID numbers
Then repeat with THAT remaining set of the same course number until they are consumed (max 24)
and continue throughout the range of data


After reading your replies, I could have simplified & worded the request a little more clearly

i will test all of the different VB scripts posted and post back to all of you
 
Upvote 0
All --
i am using the
Sub oopsie_betterExample()
Click to expand...
module

it works -- then breaks at larger than 3 rows returned --
to simplify the request i changed the id numbers to 1,2,3,4 etc

without posting a large table in the reply

create a 2 columns
A
crs


[TABLE="width: 334"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]crs
[/TD]
[TD]Id Count[/TD]
[TD]# rows to return[/TD]
[/TR]
[TR]
[TD]33004[/TD]
[TD]1[/TD]
[TD]0.04[/TD]
[/TR]
[TR]
[TD]75031[/TD]
[TD]1[/TD]
[TD]0.04[/TD]
[/TR]
[TR]
[TD]75402[/TD]
[TD]30[/TD]
[TD]1.25[/TD]
[/TR]
[TR]
[TD]75735[/TD]
[TD]15[/TD]
[TD]0.63[/TD]
[/TR]
[TR]
[TD]75736[/TD]
[TD]8[/TD]
[TD]0.33[/TD]
[/TR]
[TR]
[TD]75737[/TD]
[TD]14[/TD]
[TD]0.58[/TD]
[/TR]
[TR]
[TD]75857[/TD]
[TD]2[/TD]
[TD]0.08[/TD]
[/TR]
[TR]
[TD]76131[/TD]
[TD]5[/TD]
[TD]0.21[/TD]
[/TR]
[TR]
[TD]76839[/TD]
[TD]6[/TD]
[TD]0.25[/TD]
[/TR]
[TR]
[TD]76900[/TD]
[TD]194[/TD]
[TD]8.08[/TD]
[/TR]
[TR]
[TD]77517[/TD]
[TD]136[/TD]
[TD]5.67[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
All -- disregard post 15 i could not delete it

i am using the
Sub oopsie_betterExample()
Click to expand...
module

it works -- then breaks at larger than 3 rows returned --
to simplify the request i changed the id numbers to 1,2,3,4 etc

without posting a large table into the reply

create a 2 columns
A B
crs ID
330041
750311

<tbody>
</tbody>

[TABLE="width: 228"]
<tbody>[TR]
[TD]75402[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]75402[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]75402[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]75402[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]75402[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]75402[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]75402[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]75402[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]75402[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]75402[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]75402[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]75402[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]continue to 30 IDs[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]continue the build using many id numbers (sequential numbers are easier too "see"[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 334"]
<tbody>[TR]
[TD]crs[/TD]
[TD]Id Counts[/TD]
[TD]# rows should return[/TD]
[/TR]
[TR]
[TD]33004[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]75031[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]75402[/TD]
[TD]30[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]75735[/TD]
[TD]15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]75736[/TD]
[TD]28[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]76900[/TD]
[TD]94[/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]

Currently the Module does this when applied

[TABLE="width: 1594"]
<tbody>[TR]
[TD][/TD]
[TD]Crs[/TD]
[TD]ID #[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]33004[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]75031[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]75402[/TD]
[TD]1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]75402[/TD]
[TD]25, 26, 27, 28, 29, 30[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]75735[/TD]
[TD]1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]75736[/TD]
[TD]1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]75736[/TD]
[TD]25, 26, 27, 28[/TD]
[/TR]
[TR]
[TD]Should be[/TD]
[TD]76900[/TD]
[TD]1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009, 1010, 1011, 1012, 1013, 1014, 1015, 1016, 1017, 1018, 1019, 1020, 1021, 1022, 1023, 1024[/TD]
[/TR]
[TR]
[TD]76900[/TD]
[TD]77517[/TD]
[TD]1025, 1026, 1027, 1028, 1029, 1030, 1031, 1032, 1033, 1034, 1035, 1036, 1037, 1038, 1039, 1040, 1041, 1042, 1043, 1044, 1045, 1046, 1047, 1048[/TD]
[/TR]
[TR]
[TD]76900[/TD]
[TD]77581[/TD]
[TD]1049, 1050, 1051, 1052, 1053, 1054, 1055, 1056, 1057, 1058, 1059, 1060, 1061, 1062, 1063, 1064, 1065, 1066, 1067, 1068, 1069, 1070, 1071, 1072[/TD]
[/TR]
[TR]
[TD]76900[/TD]
[TD]77581[/TD]
[TD]1073, 1074, 1075, 1076, 1077, 1078, 1079, 1080, 1081, 1082, 1083, 1084, 1085, 1086, 1087, 1088, 1089, 1090, 1091, 1092, 1093, 1094, 1095, 1096[/TD]
[/TR]
[TR]
[TD]76900[/TD]
[TD]77827[/TD]
[TD]1097, 1098, 1099, 1100, 1101, 1102, 1103, 1104, 1105, 1106, 1107, 1108, 1109, 1110, 1111, 1112, 1113, 1114, 1115, 1116, 1117, 1118, 1119, 1120[/TD]
[/TR]
[TR]
[TD]76900[/TD]
[TD]77880[/TD]
[TD]1121, 1122, 1123, 1124, 1125, 1126, 1127, 1128, 1129, 1130, 1131, 1132, 1133, 1134, 1135, 1136, 1137, 1138, 1139, 1140, 1141, 1142, 1143, 1144[/TD]
[/TR]
[TR]
[TD]76900[/TD]
[TD]77880[/TD]
[TD]1145, 1146, 1147, 1148, 1149, 1150, 1151, 1152, 1153, 1154, 1155, 1156, 1157, 1158, 1159, 1160, 1161, 1162, 1163, 1164, 1165, 1166, 1167, 1168[/TD]
[/TR]
[TR]
[TD]76900[/TD]
[TD]78071[/TD]
[TD]1169, 1170, 1171, 1172, 1173, 1174, 1175, 1176, 1177, 1178, 1179, 1180, 1181, 1182, 1183, 1184, 1185, 1186, 1187, 1188, 1189, 1190, 1191, 1192[/TD]
[/TR]
[TR]
[TD]76900[/TD]
[TD]78071[/TD]
[TD]1193, 1194[/TD]
[/TR]
</tbody>[/TABLE]

red text is incorrect --- these are courses from data further down

Rick - I tried to use your script but got an out of range error - i could not resolve
 
Last edited:
Upvote 0
Rick Rothstein said:
Are you sure the OP's data is split like you show? His original message seems to show the data already sorted (which he can do quite quickly if desired). Assuming the data is already sorted, here is the code I came up with (output to Columns D and E on same sheet)...
Code: 
Sub CoursesAndIDs()
  Dim R As Long, X As Long, Cnt As Long, Current As Long
  Dim Data As Variant, [COLOR=#ff0000]Result As Variant[/COLOR]
  Data = Range("A2:B2").Resize(Cells(Rows.Count, "A").End(xlUp).Row - 1)
  ReDim Result(1 To UBound(Data), 1 To 2)
  For R = 1 To UBound(Data)
    If Data(R, 1) <> Current Then
      X = X + 1
      Cnt = 1
      Current = Data(R, 1)
      Result(X, 1) = Data(R, 1)
      Result(X, 2) = Data(R, 2)
    Else
      Cnt = Cnt + 1
      If Cnt > 24 Then
        X = X + 1
        Cnt = 1
        Result(X, 1) = Data(R, 1)
        Result(X, 2) = Data(R, 2)
      Else
        Result(X, 2) = Result(X, 2) & "," & Data(R, 2)
      End If
    End If
  Next
  Range("D1:E1") = Array("Course", "ID")
  Range("D2").Resize(UBound(Result), 2) = Result
End Sub
If the OP really does have split data like you show, it would be easy enough to add a line of code at the beginning of the macro to sort the original data first.
Click to expand...

Hi Rick,

Code is treating Ids as numeric data type. So it is adding Ids instead of Concatenating.

In order to work correctly, Result as Variant should be changed to Result () as string. However I'm not getting any error like Out of Range as Op said.
 
Upvote 0
Ombir said:
Hi Rick,

Code is treating Ids as numeric data type. So it is adding Ids instead of Concatenating.

In order to work correctly, Result as Variant should be changed to Result () as string.
Click to expand...
@Ombir: Good point... thanks for catching that.

@lefty38: Note the change Ombir posted in Message #19.




Ombir said:
However I'm not getting any error like Out of Range as Op said.
Click to expand...
That's why I asked the OP what line of code the error occurred on so that I could try and diagnose where the problem might be... I could not produce that error either with the test data I used.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

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