Repeating Lines of Data N Times

[Jezzzza]

I need to generate a list of users, each with a line for every 2025 holiday based on their work schedule. I have all the necessary data however I need to populate a lot of repeated data into a file I am bulk loading into our system.

Example, this work schedule:
View attachment 120254

But for this schedule I have this list of users:

In my load file I essentially need the holiday list repopulated for each user with the user ID in the same row, example:

This is showing for two users, but I'd rather not have to copy paste the list of holidays and then user IDs as I have 1000+ users

Any help would be very appreciated!

 

[Jezzzza]

The first screenshot did not load, this is the sample work schedule:

To expand on issue, I would need to A) copy the above list of days, dates, hours, etc X number of times in a list, X being the number of users assigned, then B) copy the User IDs X number of times so that each user ID is assigned to each holiday.

Thanks for any help.

 

[djclements]

It's difficult to provide a complete answer because the screenshot of your desired output contains fields not visible in your employee table or your holiday/schedule table (Project ID, Customer ID, Booking Type ID and Project Task ID). However, the generic syntax would look something like this:

=LET(
    array1, tblEmployees,
    array2, tblHolidays,
    a, SEQUENCE(ROWS(array1)),
    b, SEQUENCE(, ROWS(array2)),
    HSTACK(
        CHOOSEROWS(array1, TOCOL(IF(b, a))),
        CHOOSEROWS(array2, TOCOL(IF(a, b)))
    )
)

Replace "tblEmployees" and "tblHolidays" with the actual ranges/arrays that you want to be repeated. You could also use CHOOSECOLS to select and/or rearrange the desired columns, if necessary. For example:

=LET(
    array1, tblEmployees[Internal id],
    array2, CHOOSECOLS(tblHolidays,5,6,7,2),
    a, SEQUENCE(ROWS(array1)),
    b, SEQUENCE(, ROWS(array2)),
    HSTACK(
        CHOOSEROWS(array1, TOCOL(IF(b, a))),
        CHOOSEROWS(array2, TOCOL(IF(a, b)))
    )
)

 

[Jezzzza]

It's difficult to provide a complete answer because the screenshot of your desired output contains fields not visible in your employee table or your holiday/schedule table (Project ID, Customer ID, Booking Type ID and Project Task ID). However, the generic syntax would look something like this:

=LET(
    array1, tblEmployees,
    array2, tblHolidays,
    a, SEQUENCE(ROWS(array1)),
    b, SEQUENCE(, ROWS(array2)),
    HSTACK(
        CHOOSEROWS(array1, TOCOL(IF(b, a))),
        CHOOSEROWS(array2, TOCOL(IF(a, b)))
    )
)

Replace "tblEmployees" and "tblHolidays" with the actual ranges/arrays that you want to be repeated. You could also use CHOOSECOLS to select and/or rearrange the desired columns, if necessary. For example:

=LET(
    array1, tblEmployees[Internal id],
    array2, CHOOSECOLS(tblHolidays,5,6,7,2),
    a, SEQUENCE(ROWS(array1)),
    b, SEQUENCE(, ROWS(array2)),
    HSTACK(
        CHOOSEROWS(array1, TOCOL(IF(b, a))),
        CHOOSEROWS(array2, TOCOL(IF(a, b)))
    )
)

Thanks, I'll give it a shot.