'Build List with Attached Items

My cells look like this:

What I have

I want them to look like this with spacing between cells that need it to accommodate the repairs that were completed:

If there is any other information that would help, I would be grateful!

Solution 1:^[1]

try:

=ARRAYFORMULA({SPLIT(FLATTEN(SPLIT(QUERY(
 IF(D1:D="",,"×"&TO_TEXT(A1:A)&"?"&B1:B&"?"&C1:C&REPT("× ", 
 LEN(REGEXREPLACE(D1:D, "[^,]", )))),,9^9), "×")), "?"), 
 QUERY(TRIM(FLATTEN(IFERROR(SPLIT(D1:D, ",")))), "where Col1 is not null", )})

update 1:

=ARRAYFORMULA({SPLIT(FLATTEN(SPLIT(QUERY(
 IF((D1:D="")*(A1:A=""),,"×"&TO_TEXT(A1:A)&"?"&B1:B&"?"&C1:C&REPT("× ", 
 LEN(REGEXREPLACE(D1:D, "[^,]", )))),,9^9), "×")), "?"), 
 SUBSTITUTE(QUERY(TRIM(FLATTEN(IFERROR(SPLIT(IF((D:D="")*(A:A<>""),"¤",D1:D), ",")))), 
 "where Col1 is not null", ), "¤", )})

update 2:

=INDEX(SUBSTITUTE(QUERY(SPLIT(FLATTEN(TO_TEXT(A1:A)&"×"&B1:B&"×"&C1:C&"×"&
 TRIM(SPLIT(IF((A1:A<>"")*(D1:D=""), "?", D1:D), ","))), "×"), 
 "where Col4 is not null"), "?", ))

demo sheet

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1