How to repeat a value specified number of times in Google Sheets, assuming no overlap or ignoring overlap if any.

Screenshot of the example with ID in column B and Nights in column C.

Trying to reach desired output (column D) with reservation ID repeated the specified number of nights.

Link to

CodePudding user response：

Your sheet is "Comment only." But try deleting everything from Col D (including the header) and placing the following formula into cell D1:

=ArrayFormula({"ID Full Stay"; IF(A2:A="",,IF(ROW(A2:A)>(VLOOKUP(ROW(A2:A),FILTER(ROW(C2:C),C2:C<>""),1,TRUE) VLOOKUP(ROW(A2:A),FILTER({ROW(C2:C),C2:C-1},C2:C<>""),2,TRUE)),,VLOOKUP(ROW(A2:A),FILTER({ROW(C2:C),B2:B},C2:C<>""),2,TRUE)))})

This one formula will produce the header (which you can change as you like within the formula itself) and all results for all rows.