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.
