Google Sheets formula to concatenate column headers into a list delimited by "|", transpose it, repeat list N times

Question

I'm trying to concatenate all employee names in the attached Google form entry here (https://docs.google.com/spreadsheets/d/1S1HkO-a-xP09hZWbVsB7OeCc0KI_s3j9pwWh-N7S2Gg/edit?usp=sharing)
provided into a list delimited by "|" then use count, address and arrayformula to repeat concatenation over all cells with names in them, repeat this delimited list of names N times based on the count of number of rows in the form in "attendance by worker sheet" then split this repeated list by the "|" delimiter, to give me one cell per name and one group of names per date.

Finally, I want to transpose then paste this output down a column as shown in "desired output sheet". I want it to be automatic such that whenever an entry is made in the "attendance by worker form" it automatically updates in the "desired output sheet" I have tried

=Transpose(split(REPT('attendance by worker'!C1&"|", 4),"|"))

because I am a beginner but it is still far from what I want.

expected results as in file attached

Answer 1

you kind of need to do it like this:

=ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF('attendance by worker'!C3:Z<>"", 
 "♦"&'attendance by worker'!A3:A&"♥"&'attendance by worker'!C1:Z1&"♥"&'attendance by worker'!C3:Z, ))
 ,,999^99)),,999^99), "♦")), "♥"))

spreadsheet demo