Jan 02 2024 09:04 AM
Hi,
I have a report with a dollar amount of a purchase of tickets. The report only generates the name and dollar amount. I know how to get the dollar amount to show me the quantity. The next step is what I can't figure out. If I have 5 tickets for a $40 purchase and I need the purchaser name to show up 5 times on 5 separate lines, how do I do that?
What I have is:
Name | Purchase | Tickets |
Test | $40 | 5 |
What I need is:
Name | Purchase | Tickets | Name | Ticket Qty |
Test | $40 | 5 | Test | Ticket 1 |
Test | Ticket 2 | |||
Test | Ticket 3 | |||
Test | Ticket 4 | |||
Test | Ticket 5 |
Jan 02 2024 09:38 AM
What I need is:
May I question your use of the word "need"?
Ending up with a spreadsheet with lots of blanks in four rows of the first three columns, with the name repeated five times in the next...in general is not a good idea. There may be a valid reason for it, but that's not immediately apparent. So perhaps before a solution is presented you could explain more fully why you want it to appear that way. What are you planning to do with that array that could not be done with the "What I have" array?
Jan 02 2024 09:44 AM
Jan 02 2024 10:14 AM
Thank you. It's a start. The full process still is somewhat unclear. How, for example, are the names being entered into that virtual spin wheel? Is there not a way in that "transfer process" to have the entry automated to account for the five? Is this virtual spin wheel an altogether separate entity? Perhaps a service you're buying or renting or otherwise engaging for the event? I would think they'd have a way of accommodating this multiplier.
In any event, it would appear to be a bigger question than you originally present. You're going to be entering some single purchases, some purchases of five tickets, some of three, some of 100, etc. , etc. At the very least, I would think it would be after all of the purchases, just before final transfer to the virtual spinning wheel, that you'd want all the names to be multiplied. Which is also why I'd think that there might be a process already available at that end of things, or at least worth asking about.
Or it's a macro or VBA routine that you'd run just before that transfer of data. I'm not a VBA person, so would await the suggestions of others on this site. But you could help them by describing the full process more completely, full process from the transactions to purchase tickets through to the spinning of that virtual wheel: what are the steps as you understand them?
Jan 02 2024 12:56 PM
If you're using Excel 365, here's a solution. It's a bit long because of the padding but it's straightforward.
=LET(
Unpack, LAMBDA(a, v,
LET(
order, XLOOKUP(v, Raffle[Name], Raffle[[Purchase]:[Tickets]]),
purchase, TAKE(order, , 1),
tix, TAKE(order, , -1),
Pad, LAMBDA(scalar, EXPAND(scalar, tix, , "")),
resized_name, Pad(v),
resized_purchase, Pad(purchase),
resized_tix, Pad(tix),
unrolled_tix, "Ticket " & SEQUENCE(tix),
filled_name, EXPAND(v, tix, , v),
VSTACK(
a,
HSTACK(
resized_name,
resized_purchase,
resized_tix,
filled_name,
unrolled_tix
)
)
)
),
DROP(REDUCE("", Raffle[Name], Unpack), 1)
)
Jan 02 2024 01:40 PM
Sub raffle()
Dim i, j, k, l As Long
Range("E2:I1048576").Clear
l = Range("A" & Rows.Count).End(xlUp).Row
k = 2
For i = 2 To l
For j = 1 To Cells(i, 3).Value
If j = 1 Then
Cells(k, 5).Value = Cells(i, 1).Value
Cells(k, 6).Value = Cells(i, 2).Value
Cells(k, 7).Value = Cells(i, 3).Value
Cells(k, 8).Value = Cells(i, 1).Value
Cells(k, 9).Value = "Ticket " & j
k = k + 1
Else
Cells(k, 8).Value = Cells(i, 1).Value
Cells(k, 9).Value = "Ticket " & j
k = k + 1
End If
Next j
Next i
End Sub
Alternatively you can enter the data in columns A to C in the attached file and then run the macro by clicking the button in cell J2. The result is returned in columns E to I.
Jan 02 2024 02:37 PM
Another solution using Dynamic Array functions if you're using Excel 365. Several helping columns are used but the result in column D and E are dynamic.
Jan 03 2024 12:06 AM
Solution@TSCHEFF What version of Excel are you using? With Excel for MS365, the easiest way to repeat a value x number of times is to use the IF/SEQUENCE method. For example, if the name is in cell A2 and the number of tickets (5) is in cell C2, then the following formula would repeat the name 5 times:
=IF(SEQUENCE(C2), A2)
To return the "Ticket Qty" column, simply concatenate "Ticket " with the SEQUENCE function:
="Ticket "&SEQUENCE(C2)
The above-mentioned formulas can also be joined together using the HSTACK function to return the results in a single array:
=HSTACK(IF(SEQUENCE(C2), A2), "Ticket "&SEQUENCE(C2))
...which can be further optimized using the LET function to define a variable for SEQUENCE(C2):
=LET(seq, SEQUENCE(C2),
HSTACK(IF(seq, A2), "Ticket "&seq))
Having said that, this "simple" method will only work with one record at a time. If the final report will be a list of names, purchase amounts and tickets, the REDUCE/VSTACK method can be used to spill the results for the entire list, although it's a little more complicated. For example, with a list of names/records in range A2:C4, the following formula could be used:
=LET(arr, A2:C4,
DROP(REDUCE("", SEQUENCE(ROWS(arr)), LAMBDA(v,n, LET(
seq, SEQUENCE(INDEX(arr, n, 3)),
VSTACK(v, HSTACK(IF(seq, INDEX(arr, n, 1)), "Ticket "&seq))))), 1))
The REDUCE function is used to loop through each row in the range and apply the IF/SEQUENCE method one row at a time. The results of each iteration are then joined together using the VSTACK function. Note: you may notice performance issues with larger data sets (ie: 10,000+ records).
On a side note, this reminds me of Wyn Hopkins' Power Query Challenge - Ticket List presented a year or so ago. It's possible you may find a suitable PQ solution by viewing his video on YouTube: Convert Range of Values to a List - Power Query Challenge
Cheers!
Jan 03 2024 07:00 AM
Thank you! and thank you to everyone who offered a solution to try. This is so incredibly helpful!
Jan 03 2024 12:06 AM
Solution@TSCHEFF What version of Excel are you using? With Excel for MS365, the easiest way to repeat a value x number of times is to use the IF/SEQUENCE method. For example, if the name is in cell A2 and the number of tickets (5) is in cell C2, then the following formula would repeat the name 5 times:
=IF(SEQUENCE(C2), A2)
To return the "Ticket Qty" column, simply concatenate "Ticket " with the SEQUENCE function:
="Ticket "&SEQUENCE(C2)
The above-mentioned formulas can also be joined together using the HSTACK function to return the results in a single array:
=HSTACK(IF(SEQUENCE(C2), A2), "Ticket "&SEQUENCE(C2))
...which can be further optimized using the LET function to define a variable for SEQUENCE(C2):
=LET(seq, SEQUENCE(C2),
HSTACK(IF(seq, A2), "Ticket "&seq))
Having said that, this "simple" method will only work with one record at a time. If the final report will be a list of names, purchase amounts and tickets, the REDUCE/VSTACK method can be used to spill the results for the entire list, although it's a little more complicated. For example, with a list of names/records in range A2:C4, the following formula could be used:
=LET(arr, A2:C4,
DROP(REDUCE("", SEQUENCE(ROWS(arr)), LAMBDA(v,n, LET(
seq, SEQUENCE(INDEX(arr, n, 3)),
VSTACK(v, HSTACK(IF(seq, INDEX(arr, n, 1)), "Ticket "&seq))))), 1))
The REDUCE function is used to loop through each row in the range and apply the IF/SEQUENCE method one row at a time. The results of each iteration are then joined together using the VSTACK function. Note: you may notice performance issues with larger data sets (ie: 10,000+ records).
On a side note, this reminds me of Wyn Hopkins' Power Query Challenge - Ticket List presented a year or so ago. It's possible you may find a suitable PQ solution by viewing his video on YouTube: Convert Range of Values to a List - Power Query Challenge
Cheers!