Forum Discussion
Autofill Data
Thank you for the workbook.
Here's the formula. I treated this an 'unpacking' of the data. It's a bit longer because I had to avoid a circular reference with the totals in H.
=LET( counter, SEQUENCE(240), taken, COUNTIF(ProgramsBooked, program), available, 27 - taken, UnPack, LAMBDA(a, v, LET(resize, XLOOKUP(v, program, available, ""), VSTACK(a, EXPAND(v, resize, , v)))), Sessions_Available, DROP(REDUCE("", program, UnPack), 1), INDEX(Sessions_Available, counter) ).
Here is a modified version of the data. Some participants chose which program they wanted to attend but there are a lot that didn't which is why I have to fill in a program name. This can be random. As mentioned I can do this manually but it would save time if I could add a formula on row E327 to do as i have described previously and then drag it down to the end of the participant name list so that everyone is in a program but less than 27 in each program.
Thank you for the workbook.
Here's the formula. I treated this an 'unpacking' of the data. It's a bit longer because I had to avoid a circular reference with the totals in H.
=LET(
counter, SEQUENCE(240),
taken, COUNTIF(ProgramsBooked, program),
available, 27 - taken,
UnPack, LAMBDA(a, v, LET(resize, XLOOKUP(v, program, available, ""), VSTACK(a, EXPAND(v, resize, , v)))),
Sessions_Available, DROP(REDUCE("", program, UnPack), 1),
INDEX(Sessions_Available, counter)
)
.
- Awesome thank you, it seems it wasn't as straightforward as I had thought. I appreciate the time you put into this!
- You're welcome! Glad I could help.