Google Sheets: Repeat values as rows based on associated numbers-CodePudding

I have a table in Sheets that has 2 columns: (1) Station (2) Number of people at Station:

Station     Number of People at Station
      A                               1
      B                               2
      C                               1
      D                               4

I want to have a new column in excel populate with Station names repeating in it's own row based on number of people at station. The expected output would be below:

Station Allocation
                 A
                 B
                 B
                 C
                 D
                 D
                 D
                 D

Is there a way to dynamically have this final table change based on the number of people at station? So if someone was to use a google sheet and they change Station A to 2 people, then there will now be 2 rows with A instead of 1.

CodePudding user response：

Try:

=arrayformula({"Values";query(flatten(if(B2:B<>"",split(rept(A2:A&"|",B2:B),"|"),)),"where Col1 is not null",1)})

Replace "|" with a character that is not used in your dataset.