multiple entries from existing excel file

Question

our customer provides us with an excel file listing drivers names and the number of tickets they get for a free drawing, such as "bill smith (next column) 17 tickets

is there a way to take that and make it 17 separate entries for "bill Smith" so we can print out upwards of 5000 tickets total for all 286 drivers? some get 1, some 5, some more...

we currently manually repeat the drivers nameas many time as required.

we print them by doing a mail merge to a 2x3 avery label template then cut them apart.

Iszi · Answer 1 · 2014-09-03T05:32:05.903

Modify the file such that it has the following properties:
- Column A: Header is "Name". All cells contain the names of people to be issued tickets.
- Column B: Header is "Number". All cells contain the number of tickets to be assigned to the person listed on the same row in column A. No other text is included.
- No other data is included on the sheet with "Name" and "Number" info.
With the sheet containing "Name" and "Number" info selected, save the file as CSV (comma delimited). For the sake of this example, we'll use OrigCSV.csv for the file name.
Open a PowerShell session, and navigate to the folder containing the CSV you just saved.
Run the following command:
- $x=ipcsv .\OrigCSV.csv;$x|%{for($y=1;$y-le$_.Number;$y++){$_.Name}}|Out-File NewCSV.csv
Open NewCSV.csv and verify the names are listed in the manner and number you want.

If you need more than just the name duplicated, it's still possible with PowerShell - just a bit more "interesting".

Here's an expanded, and commented, version of the command line provided above:

<#
    First set $x so that it contains everything in OrigCSV.csv.
    Each line of the CSV will be an array element within $x, with "Name" and "Number" properties according to their entry in the CSV.

    ipcsv is a built-in alias for Import-Csv
#>
$x=ipcsv .\OrigCSV.csv;
<#
    Next step is to put all the objects in $x through a ForEach-Object loop.

    % is a built-in alias for ForEach-Object.
#>
$x|%{
    <#
        Within ForEach-Object, we're starting a For loop.
        The loop definition starts with setting a counter, $y, to 1.
        Then, if $y is less than or equal to the current line item's "Number" property, the script block will execute.
        After the script block executes, it will increment $y by 1 and check the loop condition again.
        Once $y becomes greater than the current line item's "Number" property, the For loop will exit.
    #>
    for($y=1;$y-le$_.Number;$y++)
    {
        # This next line simply outputs the "Name" property of the current line item.
        $_.Name
    # After the For loop exits, the script will return to the ForEach-Object loop and proceed to put the next item into the For loop.
    }
# After ForEach-Object is done with its work, we pipe all of the output to Out-File so that the list gets written to a new CSV file.
}|Out-File NewCSV.csv

score 0 · Answer 2 · edited Mar 20 '17 at 10:04

Here is a VBA solution. First, select the data you have in two columns. Do not select the column headers if they exist.

Next, place this code in a module and execute it. (For instructions on doing this, see this post.)

Sub TicketList()
'Two columns of drivers and ticket counts should be selected (no headers) before running this Sub.
Dim drivers() As Variant, output() As Variant, shtOut As Worksheet
Dim i As Long, j As Long, k As Long, scount As Integer
drivers = Selection.Value
'Set size of output array to match total number of tickets
ReDim output(1 To Application.WorksheetFunction.Sum(Selection), 1 To 1) As Variant
For i = LBound(drivers, 1) To UBound(drivers, 1)
    For j = 1 To drivers(i, 2)
        k = k + 1
        output(k, 1) = drivers(i, 1)
    Next j
Next i
'Place tickets on new sheet named "Driver Tickets #"
For Each sht In ThisWorkbook.Sheets
    If InStr(sht.Name, "Driver Tickets") > 0 Then scount = scount + 1
Next sht
Set shtOut = Sheets.Add
If scount = 0 Then
    shtOut.Name = "Driver Tickets"
Else
    shtOut.Name = "Driver Tickets " & CStr(scount + 1)
End If
'Print output on the new sheet
shtOut.Range("A1").Resize(UBound(output, 1), 1).Value = output
End Sub

This will create the list of names for tickets on a new sheet named "Driver Tickets".

multiple entries from existing excel file

2 Answers2