How to create a dynamic VSTACK comprised of arrays formed with MAKEARRAY in Google Sheets
This is probably a simple one but it is eluding me somehow. We are a security company managing access control systems in multi-unit residential buildings.
I have a Google sheet that we give to our clients to fill out that comes back to us containing names of tenants in a building, their apt #, and a number of access control cards to be issued to each person.
From that, I need to generate another sheet that has a vstack with a row for each card to be issued so we can record the serial number of the cards and who they were issued to.
If the first sheet contains:
| Name | Apartment | Value |
|---|---|---|
| Bob | Apt 2 | 5 |
| Jill | Apt 5 | 2 |
| Sam | Apt 1 | 0 |
| George | Apt 7 | 1 |
Blanks or zeroes in the 3rd column should be ignored. The second sheet should look as so:
| Name | Apartment |
|---|---|
| Bob | Apt 2 |
| Bob | Apt 2 |
| Bob | Apt 2 |
| Bob | Apt 2 |
| Bob | Apt 2 |
| Jill | Apt 5 |
| Jill | Apt 5 |
| George | Apt 7 |
The formula needs to be dynamic so that the first sheet can contain as many or as few rows as needed for the particular building.
I have messed around with MAKEARRAY which does a fine job of producing an array from a single row in Sheet 1 but I was unable to figure out how to stack those arrays dynamically in Sheet 2 using VSTACK.
You can use REDUCE:
=ARRAYFORMULA(REDUCE(A1:B1,A2:INDEX(A:A,MATCH(,0/LEN(A:A))),LAMBDA(a,c,
VSTACK(a,IFERROR(IF(SEQUENCE(OFFSET(c,,2)),{c,OFFSET(c,,1)}),TOCOL(,1))))))
You can also do this with string concatenation and SPLIT.
=ARRAYFORMULA(LET(v,TOCOL(C2:C,1),SPLIT(TOCOL(IF(
SEQUENCE(1,MAX(v))>v,,TOCOL(A2:A,1)&"❄"&TOCOL(B2:B,1)),1),"❄")))
