Build List with Attached Items

Question

My cells look like this:

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!

Answer 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