How to number sequential items and put number all numbers into a cell with a delimeter

T

theloveofwisdom

New Member
Joined
Nov 20, 2015
Messages
10
Please help me write a vba that can create the data in the second column. Any help will be appreciated. There will be a large amount of tickets, and each individual may purchase as many tickets as they like... probably no more than a few hounded.

[TABLE="width: 247"]
<tbody>[TR]
[TD]# of tickets[/TD]
[TD]ticket #[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1,2;3;4[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]5;6;7[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]8;9[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]10;11;12;13;14;15[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]17;18;19[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
Thank you all. I have a followup request.

What if I want to distinguish between two types of ticket orders. This would seem to need two different counters dependent on the column for # of tickets and the column for Order type. Could the any of the above functions code be modified to have two counters so as to generate the output below in the Ticket #s column? A modified version of any of the previous code samples (which all work for the initial request) will do, or a completely new one also.


The only other option I have, which I will probably resort to, is to have two different sheet- one for Mail orders, and one for Online orders. If anyone can chime in with another piece of code, that would be great, but you all have helpful beyond words already.



[TABLE="class: cms_table, width: 330"]
<tbody>[TR]
[TD="align: center"]First Name[/TD]
[TD="align: center"]Last Name[/TD]
[TD="align: center"]Order Type[/TD]
[TD="align: center"]# of Tickets[/TD]
[TD="align: center"]Ticket #s[/TD]
[/TR]
[TR]
[TD="align: center"]Joe[/TD]
[TD="align: center"]Smith[/TD]
[TD="align: center"]Online[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]i1;i2;i3;i4;i5;i6;i7[/TD]
[/TR]
[TR]
[TD="align: center"]Steve[/TD]
[TD="align: center"]Jones[/TD]
[TD="align: center"]Mail[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]m1;m2;m3;m4;m5[/TD]
[/TR]
[TR]
[TD="align: center"]Nick[/TD]
[TD="align: center"]Smith[/TD]
[TD="align: center"]Online[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]i8;i9;i10;i11;i12[/TD]
[/TR]
[TR]
[TD="align: center"]Nick[/TD]
[TD="align: center"]Jones[/TD]
[TD="align: center"]Online[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]i13;i14;i15;i16;i17;i18;i19;i20;i21[/TD]
[/TR]
[TR]
[TD="align: center"]Nick[/TD]
[TD="align: center"]Stevens[/TD]
[TD="align: center"]Mail[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]m6;m7;m8;m9;m10;m11[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
I need to go offline right now, but will look at this later if someone else hasn't provided a solution for you.
 
Upvote 0
theloveofwisdom said:
Thank you all. I have a followup request.

What if I want to distinguish between two types of ticket orders. This would seem to need two different counters dependent on the column for # of tickets and the column for Order type. Could the any of the above functions code be modified to have two counters so as to generate the output below in the Ticket #s column? A modified version of any of the previous code samples (which all work for the initial request) will do, or a completely new one also.


The only other option I have, which I will probably resort to, is to have two different sheet- one for Mail orders, and one for Online orders. If anyone can chime in with another piece of code, that would be great, but you all have helpful beyond words already.



[TABLE="class: cms_table, width: 330"]
<tbody>[TR]
[TD="align: center"]First Name[/TD]
[TD="align: center"]Last Name[/TD]
[TD="align: center"]Order Type[/TD]
[TD="align: center"]# of Tickets[/TD]
[TD="align: center"]Ticket #s[/TD]
[/TR]
[TR]
[TD="align: center"]Joe[/TD]
[TD="align: center"]Smith[/TD]
[TD="align: center"]Online[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]i1;i2;i3;i4;i5;i6;i7[/TD]
[/TR]
[TR]
[TD="align: center"]Steve[/TD]
[TD="align: center"]Jones[/TD]
[TD="align: center"]Mail[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]m1;m2;m3;m4;m5[/TD]
[/TR]
[TR]
[TD="align: center"]Nick[/TD]
[TD="align: center"]Smith[/TD]
[TD="align: center"]Online[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]i8;i9;i10;i11;i12[/TD]
[/TR]
[TR]
[TD="align: center"]Nick[/TD]
[TD="align: center"]Jones[/TD]
[TD="align: center"]Online[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]i13;i14;i15;i16;i17;i18;i19;i20;i21[/TD]
[/TR]
[TR]
[TD="align: center"]Nick[/TD]
[TD="align: center"]Stevens[/TD]
[TD="align: center"]Mail[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]m6;m7;m8;m9;m10;m11[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
This modification should do it.
Code: 
Sub TicketNums()
Const O As String = "Online"
Const M As String = "Mail"
Dim R As Range, Vin As Variant, Vout As Variant, Cum As Long, First  As Long, j As Long, k As Long, S As String
Set R = Range("A2:D" & Cells(Rows.Count, "A").End(xlUp).Row)
Vin = R.Value
ReDim Vout(1 To UBound(Vin, 1), 1 To 1)
First = 1
For j = LBound(Vin, 1) To UBound(Vin, 1)
       Cum = Cum + Val(Vin(j, 4))
       For k = First To Cum
        Select Case Vin(j, 3)
            Case O: S = S & ";" & "i" & k
            Case M: S = S & ";" & "m" & k
        End Select
       Next k
       Vout(j, 1) = Right(S, Len(S) - 1)
       First = Cum + 1
       S = ""
Next j
Application.ScreenUpdating = False
R.Columns(4).Offset(0, 1).Value = Vout
R.Columns(4).Offset(0, 1).EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Thank you very much for your time and input. However, I think there is a slight mistake in the code below. If you look closely, the ticket counter for Case M should be independant of the that for Case O- such that the first Mail ticket should be numbered "m1;" while the code below generates a "m8;". If you have time, It would be great to get this running.



[TABLE="class: cms_table_cms_table, width: 330"]
<tbody>[TR]
[TD="align: center"]First Name[/TD]
[TD="align: center"]Last Name[/TD]
[TD="align: center"]Order Type[/TD]
[TD="align: center"]# of Tickets[/TD]
[TD="align: center"]Ticket #s[/TD]
[/TR]
[TR]
[TD="align: center"]Joe[/TD]
[TD="align: center"]Smith[/TD]
[TD="align: center"]Online[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]i1;i2;i3;i4;i5;i6;i7[/TD]
[/TR]
[TR]
[TD="align: center"]Steve[/TD]
[TD="align: center"]Jones[/TD]
[TD="align: center"]Mail[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]m1;m2;m3;m4;m5[/TD]
[/TR]
[TR]
[TD="align: center"]Nick[/TD]
[TD="align: center"]Smith[/TD]
[TD="align: center"]Online[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]i8;i9;i10;i11;i12[/TD]
[/TR]
[TR]
[TD="align: center"]Nick[/TD]
[TD="align: center"]Jones[/TD]
[TD="align: center"]Online[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]i13;i14;i15;i16;i17;i18;i19;i20;i21[/TD]
[/TR]
[TR]
[TD="align: center"]Nick[/TD]
[TD="align: center"]Stevens[/TD]
[TD="align: center"]Mail[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]m6;m7;m8;m9;m10;m11[/TD]
[/TR]
</tbody>[/TABLE]



JoeMo said:
This modification should do it.
Code: 
Sub TicketNums()
Const O As String = "Online"
Const M As String = "Mail"
Dim R As Range, Vin As Variant, Vout As Variant, Cum As Long, First  As Long, j As Long, k As Long, S As String
Set R = Range("A2:D" & Cells(Rows.Count, "A").End(xlUp).Row)
Vin = R.Value
ReDim Vout(1 To UBound(Vin, 1), 1 To 1)
First = 1
For j = LBound(Vin, 1) To UBound(Vin, 1)
       Cum = Cum + Val(Vin(j, 4))
       For k = First To Cum
        Select Case Vin(j, 3)
            Case O: S = S & ";" & "i" & k
            Case M: S = S & ";" & "m" & k
        End Select
       Next k
       Vout(j, 1) = Right(S, Len(S) - 1)
       First = Cum + 1
       S = ""
Next j
Application.ScreenUpdating = False
R.Columns(4).Offset(0, 1).Value = Vout
R.Columns(4).Offset(0, 1).EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub
Click to expand...
 
Upvote 0
Guess I didn't look closely. :nya: Try this:
Code: 
Sub TicketNums()
Const O As String = "Online"
Const M As String = "Mail"
Dim R As Range, Vin As Variant, Vout As Variant, CumM As Long, CumO As Long, _
    FirstM As Long, FirstO As Long, j As Long, k As Long, SM As String, SO As String
Set R = Range("A2:D" & Cells(Rows.Count, "A").End(xlUp).Row)
Vin = R.Value
ReDim Vout(1 To UBound(Vin, 1), 1 To 1)
FirstO = 1
FirstM = 1
For j = LBound(Vin, 1) To UBound(Vin, 1)
    Select Case Vin(j, 3)
        Case O
            CumO = CumO + Val(Vin(j, 4))
            For k = FirstO To CumO
                SO = SO & ";" & "i" & k
            Next k
            Vout(j, 1) = Right(SO, Len(SO) - 1)
            SO = ""
            FirstO = CumO + 1
        Case M
            CumM = CumM + Val(Vin(j, 4))
            For k = FirstM To CumM
                SM = SM & ";" & "m" & k
            Next k
            Vout(j, 1) = Right(SM, Len(SM) - 1)
            SM = ""
            FirstM = CumM + 1
    End Select
Next j
Application.ScreenUpdating = False
R.Columns(4).Offset(0, 1).Value = Vout
R.Columns(4).Offset(0, 1).EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub
 
Upvote 0
To slightly beat a dead horse, try this in E2

=IF(D2="Online", "l", "m")&NumeralSequence(SUM(D$1:D1)+1,SUM(D$1:D2), ";"&IF(C1="Online", "l", "m"))
 
Upvote 0
theloveofwisdom said:
Please help me write a vba that can create the data in the second column. Any help will be appreciated. There will be a large amount of tickets, and each individual may purchase as many tickets as they like... probably no more than a few hunded.

[TABLE="class: cms_table, width: 330"]
<tbody>[TR]
[TD="align: center"]First Name[/TD]
[TD="align: center"]Last Name[/TD]
[TD="align: center"]Order Type[/TD]
[TD="align: center"]# of Tickets[/TD]
[TD="align: center"]Ticket #s[/TD]
[/TR]
[TR]
[TD="align: center"]Joe[/TD]
[TD="align: center"]Smith[/TD]
[TD="align: center"]Online[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]i1;i2;i3;i4;i5;i6;i7[/TD]
[/TR]
[TR]
[TD="align: center"]Steve[/TD]
[TD="align: center"]Jones[/TD]
[TD="align: center"]Mail[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]m1;m2;m3;m4;m5[/TD]
[/TR]
[TR]
[TD="align: center"]Nick[/TD]
[TD="align: center"]Smith[/TD]
[TD="align: center"]Online[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]i8;i9;i10;i11;i12[/TD]
[/TR]
[TR]
[TD="align: center"]Nick[/TD]
[TD="align: center"]Jones[/TD]
[TD="align: center"]Online[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]i13;i14;i15;i16;i17;i18;i19;i20;i21[/TD]
[/TR]
[TR]
[TD="align: center"]Nick[/TD]
[TD="align: center"]Stevens[/TD]
[TD="align: center"]Mail[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]m6;m7;m8;m9;m10;m11[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
I think this macro will also do what you want...
Code: 
[table="width: 500"]
[tr]
	[td]Sub TicketNumbers()
  Dim R As Long, LastI As Long, LastM As Long, TempLast As Long, Temp As String, Online As Boolean
  Application.ScreenUpdating = False
  For R = 2 To Cells(Rows.Count, "C").End(xlUp).Row
    Online = LCase(Cells(R, "C").Value) = "online"
    TempLast = IIf(Online, LastI, LastM) + Cells(R, "D").Value
    Temp = IIf(Online, "i", "m") & Join(Evaluate("TRANSPOSE(ROW(" & IIf(Online, LastI, LastM) + 1 & ":" & TempLast & "))"), IIf(Online, ";i", ";m"))
    If Online Then LastI = TempLast Else LastM = TempLast
    Cells(R, "E").Value = Temp
  Next
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]
 
Last edited:
Upvote 0
theloveofwisdom said:
Excellent!! This worked for me. This community is awesome. Thank you very much!
Click to expand...
It is not clear whose code worked for you nor who it is that you are thanking, but I am sure the several participants in this thread will join me in saying a collective, "You are quite welcome".
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
Put amount in DEBIT,CREDIT columns based on matching part of item
Replies
9
Views
360
Omran Y
O
D
  • Question Question
How to keep a tally of matching letters in separate cells
Replies
4
Views
788
Davenil
D
S
  • Solved
Seperate Data In One Cell Across Multiple Cells
Replies
6
Views
2K
Alex Blakenburg
A
D
  • Solved
Association Rule mining using excel; Count how many rows have two searched for numbers
2
Replies
14
Views
1K
Fluff
Fluff
C
  • Question Question
Formula to find Max number of items in orders
Replies
1
Views
732
fjns
F

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