Forum Discussion

Copper Contributor

Jan 02, 2024

Solved

How to expand one amount in one cell into multiple lines of data

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

Formulas and Functions

djclements
Jan 03, 2024
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))

Repeat with one record

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))

Repeat with multiple records

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!

8 Replies

djclements
Bronze Contributor
Jan 03, 2024
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))

Repeat with one record

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))

Repeat with multiple records

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!
- TSCHEFF
  Copper Contributor
  Jan 03, 2024
  Thank you! and thank you to everyone who offered a solution to try. This is so incredibly helpful!
DaeyunPablo
Brass Contributor
Jan 02, 2024
TSCHEFF
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.
Raffle_Repeat_Names.xlsx14 KB

Patrick2788

Silver Contributor

Jan 02, 2024

TSCHEFF

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)
)

Raffle 1.2.xlsx22 KB

mathetes
Silver Contributor
Jan 02, 2024
TSCHEFF

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?
- TSCHEFF
  Copper Contributor
  Jan 02, 2024
  We are selling raffle tickets at an event, but rather than giving out physical tickets we'll be entering names of individuals into a virtual spin the wheel. If someone buys 5 tickets I need their name to populate 5 times to ensure they have 5 entries for the drawing. The report from our system doesn't give me their name 5 times, just the dollar amount for the total purchase. I know the formula to get the purchase price to equal the ticket quantity, but I don't know how to get the ticket purchaser name to populate for each ticket.
  I don't need this spreadsheet to live on after our event so empty cells are ok. We'll end up doing a copy and paste of the purchaser name to enter into the spin the wheel site.
  Does this explanation help to better explain my need?
  - OliverScheurich
    Gold Contributor
    Jan 02, 2024
    TSCHEFF
    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.
    raffle.xlsm15 KB

Resources

=LET(\n Unpack, LAMBDA(a, v,\n LET(\n order, XLOOKUP(v, Raffle[Name], Raffle[[Purchase]:[Tickets]]),\n purchase, TAKE(order, , 1),\n tix, TAKE(order, , -1),\n Pad, LAMBDA(scalar, EXPAND(scalar, tix, , \"\")),\n resized_name, Pad(v),\n resized_purchase, Pad(purchase),\n resized_tix, Pad(tix),\n unrolled_tix, \"Ticket \" & SEQUENCE(tix),\n filled_name, EXPAND(v, tix, , v),\n VSTACK(\n a,\n HSTACK(\n resized_name,\n resized_purchase,\n resized_tix,\n filled_name,\n unrolled_tix\n )\n )\n )\n ),\n DROP(REDUCE(\"\", Raffle[Name], Unpack), 1)\n)

Sub raffle()\n\nDim i, j, k, l As Long\n\nRange(\"E2:I1048576\").Clear\n\nl = Range(\"A\" & Rows.Count).End(xlUp).Row\nk = 2\nFor i = 2 To l\n\nFor j = 1 To Cells(i, 3).Value\n\nIf j = 1 Then\nCells(k, 5).Value = Cells(i, 1).Value\nCells(k, 6).Value = Cells(i, 2).Value\nCells(k, 7).Value = Cells(i, 3).Value\nCells(k, 8).Value = Cells(i, 1).Value\nCells(k, 9).Value = \"Ticket \" & j\n\nk = k + 1\n\nElse\n\nCells(k, 8).Value = Cells(i, 1).Value\nCells(k, 9).Value = \"Ticket \" & j\nk = k + 1\nEnd If\n\nNext j\n\nNext i\n\nEnd Sub