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:
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:
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.
Shawn is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.