Sharepoint formula find

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!