Creating Column of seats from a single line description in Google Sheets

Question

we are booking a training and we are trying to convert a description of what seats we have into a columnn list so we can automatically allocate our students to a class.

Eg the the description of the seats description cell looks like:

Cell F5: TrainingRoom1/E 17-18 (2) , TrainingRoom1/J 21-24 (4) , TrainingRoom1/F 19-21 (3) , TrainingRoom1/H 21-24 (4) , TrainingRoom1/K 21-25 (5) , TrainingRoom1/N 21-24 (4) , TrainingRoom1/M 21-25 (5) , TrainingRoom1/P 23-25 (3)

We want to turn this into a couple of columns that looks like:

ColumnA:Room ,
ColumnB:SeatNumber
ColumnC:Email (will be allocated later)

Results would look like:

TrainingRoom1 --- E17
TrainingRoom1 --- E18

z.. · Accepted Answer · 2024-05-09 14:18:08Z

1

Here's a possible solution:

=ARRAYFORMULA(LET(
   data,WRAPROWS(SPLIT(F5,"/ (),-"),5),
   SPLIT(
     REDUCE(TOCOL(,1),SEQUENCE(ROWS(data)),
       LAMBDA(a,i,VSTACK(a,
         INDEX(data,i,1)&"|"&INDEX(data,i,2)&
         SEQUENCE(INDEX(data,i,5),1,INDEX(data,i,3))))),"|")))

answered May 9, 2024 at 14:18

z..

14k2 gold badges10 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating Column of seats from a single line description in Google Sheets

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related