Create a table based on data in 2 unequal columns

Question

In a google sheets spreadsheet I have a list of consecutive week day dates (dd/mm/yyyy)in column A and a list of user names in column B. I'd like to create a table across 2 columns that lists a single user name record for each date from column A, therefore each date will be duplicated to represent a record for each user name, and each user name will be duplicated according to the number of dates in my list. Although I know how to achieve this next part, for context, I need the result as a table that I can then use as criteria for a VLookup in another range which consists of clock-in and clock-out times per user per day (i.e. if a clock-in record exists in the list of clock-in records for the date from this new table I have created and the user name from this new table, then return that clock in time otherwise return "-") . Ultimately I need to end up with a table that lists all users on each working day and lists their clock in and clock out time for that day. If there is no clock in or clock out record on a certain day for a certain user, I still want a record in my end table so that it is clear that there was no clock in or clock out record.

Currently my list has 365 date records in column A and 14 user names in column B so my new table should end up with 365*14=5110 rows. To simplify with less rows (5 dates and 2 names), here is an example of what I have:

Column A	Column B
12/10/2022	James
13/10/2022	Amy
14/10/2022
17/10/2022
18/10/2022

And this is what I'm expecting in another area on my sheet:

Column C	Column D
12/10/2022	James
12/10/2022	Amy
13/10/2022	James
13/10/2022	Amy
14/10/2022	James
14/10/2022	Amy
17/10/2022	James
17/10/2022	Amy
18/10/2022	James
18/10/2022	Amy

Any idea what formula I could use in Cell C1 to get the desired result?

Answer 1

You may try:

=reduce(tocol(,1),tocol(A2:A,1),lambda(a,c,let(Σ,tocol(B2:B,1),vstack(a,hstack(chooserows(c,sequence(counta(Σ),1,1,0)),Σ)))))