Generate multiple rows of data based on inputs

[code_noob1985]

hi there
I'm need to auto-generate a sequence of records in a table based on a series of inputs. Clicking the button will trigger the auto-generation.
The yellow cells (Col C) are drop down boxes referencing data in the grey area.
The orange cells (Col C) are free text.
The data in Col D is a lookup of Col C selections from the grey tables. Except for D12 - which is an abbreviation for Customer Name (currently formula but could be free text as well).
When the user clicks the Generate button, it should add data to the Seat table on the right. The Seat Ref column will be a combination of the data in Col D separated by "-" and ending with a ticket number.
The number of tickets will dictate the number of ticket references to generate and populate in the table.
Could you help with the code needed to generate this?

 

[kvsrinivasamurthy]

Pl post the data in XL2BB format.

 

[code_noob1985]

Sample Seat Ref Generator.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
2		Select Option	Hidden Ref
3					Lookup Tables									Seating Table
4	Rep Name	Mary	M		Rep Name		Location		Speaker		Section			Location	Speaker	Section	Rep	Customer Name	Ticket Ref
5					John	J	Brisbane	BNE	My Band	MB	Ground Floor	GR		John	Brisbane	My Band	Ground Floor	John Smith	J-BNE-MB-GR-JSm-01
6	Location	Brisbane	BNE		Mary	M	Sydney	SYD	Your Band	YB	Gallery	GL		John	Brisbane	My Band	Ground Floor	John Smith	J-BNE-MB-GR-JSm-02
7					Steve	S	Melbourne	MEL	Their Band	TB				Mary	Brisbane	Your Band	Gallery	Bill Murray	M-BNE-YB-GL-BMu-01
8	Artist	Your Band	YB											Mary	Brisbane	Your Band	Gallery	Bill Murray	M-BNE-YB-GL-BMu-02
9														Mary	Brisbane	Your Band	Gallery	Bill Murray	M-BNE-YB-GL-BMu-03
10	Section	Gallery	GL											Mary	Brisbane	Your Band	Gallery	Bill Murray	M-BNE-YB-GL-BMu-04
11
12	Customer Name	Bill Murray	BMu
13
14	Number of Tickets	4
15
16
17
18
19
20
Seat Ref

Cell Formulas
Range	Formula
D4	D4	=IFERROR(VLOOKUP(C4,$F$5:$G$7,2,FALSE),"")
D6	D6	=IFERROR(VLOOKUP(C6,$H$5:$I$7,2,FALSE),"")
D8	D8	=IFERROR(VLOOKUP(C8,$J$5:$K$7,2,FALSE),"")
D10	D10	=IFERROR(VLOOKUP(C10,$L$5:$M$6,2,FALSE),"")
D12	D12	=LEFT(C12,1)&MID(C12,FIND(" ",C12,1)+1,2)

Cells with Data Validation
Cell	Allow	Criteria
C4	List	=$F$5:$F$7
C6	List	=$H$5:$H$7
C8	List	=$J$5:$J$7
C10	List	=$L$5:$L$6

 

[Sanjeev1976]

You can try the below code and create a button to run it :

Sub GenerateTickets()
    Dim ws As Worksheet
    Dim lastRow As Long, newRow As Long
    Dim numTickets As Integer
    Dim i As Integer
    Dim rep As String, location As String, artist As String, section As String, customer As String
    Dim ticketPrefix As String
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your actual sheet name
    
    ' Find the last row in column O ("Rep Name" is in Column O)
    lastRow = ws.Cells(ws.Rows.Count, 15).End(xlUp).Row
    
    numTickets = ws.Range("C14").Value
    
    ' Read details from columns
    rep = ws.Range("C4").Value
    location = ws.Range("C6").Value
    artist = ws.Range("C8").Value
    section = ws.Range("C10").Value
    customer = ws.Range("C12").Value
    
    ' Construct the ticket prefix
    ticketPrefix = ws.Range("D4").Value & "-" & ws.Range("D6").Value & "-" & ws.Range("D8").Value & "-" & ws.Range("D10").Value & "-" & ws.Range("D12").Value & "-"
    
    ' Determine the new starting row for ticket entries
    newRow = lastRow + 1
    
    ' Generate tickets and place them in the next available rows
    For i = 1 To numTickets
        ws.Cells(newRow, 15).Value = rep
        ws.Cells(newRow, 16).Value = location
        ws.Cells(newRow, 17).Value = artist
        ws.Cells(newRow, 18).Value = section
        ws.Cells(newRow, 19).Value = customer
        ws.Cells(newRow, 20).Value = ticketPrefix & Format(i, "00") ' Numbering in two-digit format
        newRow = newRow + 1
    Next i
    
    MsgBox "Tickets Generated Successfully!", vbInformation
End Sub

 

[HighAndWilder]

hi there
I'm need to auto-generate a sequence of records in a table based on a series of inputs. Clicking the button will trigger the auto-generation.
The yellow cells (Col C) are drop down boxes referencing data in the grey area.
The orange cells (Col C) are free text.
The data in Col D is a lookup of Col C selections from the grey tables. Except for D12 - which is an abbreviation for Customer Name (currently formula but could be free text as well).
When the user clicks the Generate button, it should add data to the Seat table on the right. The Seat Ref column will be a combination of the data in Col D separated by "-" and ending with a ticket number.
The number of tickets will dictate the number of ticket references to generate and populate in the table.
Could you help with the code needed to generate this?

Give this a go linked to a button.

Test it on a copy of your data.

It does not protect the user from clicking on the button more than once thereby duplicating the tickets.

Public Sub GenerateTickets()
Dim rng As Range
  
  With Worksheets("Seat Ref")
    Set rng = .Range("O" & .Cells(.Rows.Count, 15).End(xlUp).Row + 1)
    rng.Formula2 = "=HSTACK(TEXTSPLIT(REPT(TEXTJOIN("","",,C4:C12)&"",""&" & """|"",C14),"","",""|""," & "TRUE)," & _
      "CONCATENATE(TEXTSPLIT(REPT(TEXTJOIN(" & """-"",,D4:D12)&""|"",C14),," & """|"",TRUE)," & """-"",RIGHT(" & """00"" & SEQUENCE(" & "C14" & ",1,1,1),2)))"
    rng.Resize(Range("C14"), 6).Value = rng.Resize(Range("C14"), 6).Value
  End With
    
End Sub

 

[kvsrinivasamurthy]

Create a button on the worksheet using developer tab and assign this macro to the button.
Try this code.

Sub FillSeatTable()
Dim A, B, Nm, M, K
Dim T&, Ro&

A = Range("F5:M" & Range("F5").End(xlDown).Row)
B = Range("O5:S" & Range("O5").End(xlDown).Row)
ReDim R(1 To UBound(B, 1), 1 To 1)
Nm = WorksheetFunction.Index(A, 0, 1)

With CreateObject("scripting.dictionary")
For T = 1 To UBound(B, 1)
Ro = WorksheetFunction.Match(B(T, 1), Nm, 0)
M = Split(B(T, 5), " ")
K = A(Ro, 2) & "-" & A(Ro, 4) & "-" & A(Ro, 6) & "-" & A(Ro, 8) & "-" & Left(M(0), 1) & Left(M(1), 2)
If .exists(K) Then
.Item(K) = .Item(K) + 1
Else
.Item(K) = 1
End If

R(T, 1) = K & "-" & Format(.Item(K), "00")

Next T
End With

Range("T5").Resize(UBound(B, 1), 1) = R

End Sub

 

[Fuji]

Simply...

Sub test()
    Dim f, x
    x = Application.Match([c4], Columns("f"), 0)
    If IsError(x) Then Exit Sub
    f = Application.Substitute(Array("=f#", "=h#", "=j#", "=l#", "=c$12", _
        "=textjoin(""-"",true,d$4:d$12)&text(row(a1),""-00"")"), "#", "$" & x)
    With Range("o" & Rows.Count).End(xlUp)(2).Resize(, UBound(f))
        .Formula = f
        .AutoFill .Resize([c14])
        .Resize([c14]).Value = .Resize([c14]).Value
    End With
End Sub

 

[HighAndWilder]

Simply...

Sub test()
    Dim f, x
    x = Application.Match([c4], Columns("f"), 0)
    If IsError(x) Then Exit Sub
    f = Application.Substitute(Array("=f#", "=h#", "=j#", "=l#", "=c$12", _
        "=textjoin(""-"",true,d$4:d$12)&text(row(a1),""-00"")"), "#", "$" & x)
    With Range("o" & Rows.Count).End(xlUp)(2).Resize(, UBound(f))
        .Formula = f
        .AutoFill .Resize([c14])
        .Resize([c14]).Value = .Resize([c14]).Value
    End With
End Sub

A technique new to me so added to my library.

 

[code_noob1985]

Give this a go linked to a button.

Test it on a copy of your data.

It does not protect the user from clicking on the button more than once thereby duplicating the tickets.

Public Sub GenerateTickets()
Dim rng As Range
 
  With Worksheets("Seat Ref")
    Set rng = .Range("O" & .Cells(.Rows.Count, 15).End(xlUp).Row + 1)
    rng.Formula2 = "=HSTACK(TEXTSPLIT(REPT(TEXTJOIN("","",,C4:C12)&"",""&" & """|"",C14),"","",""|""," & "TRUE)," & _
      "CONCATENATE(TEXTSPLIT(REPT(TEXTJOIN(" & """-"",,D4:D12)&""|"",C14),," & """|"",TRUE)," & """-"",RIGHT(" & """00"" & SEQUENCE(" & "C14" & ",1,1,1),2)))"
    rng.Resize(Range("C14"), 6).Value = rng.Resize(Range("C14"), 6).Value
  End With
   
End Sub

thanks - tried this and it works. If the table I'm pasting into is in a different worksheet, what adjustment would be needed for this code?