Forum Discussion

TSCHEFF's avatar
TSCHEFF
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 ...
Formulas and Functions
  • djclements's avatar
    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!

Patrick2788's avatar
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)
)

Resources