Search code examples
google-sheetsdynamicgoogle-sheets-formulavstack

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.

Solution

  • 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))))))

    enter image description here

    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),"❄")))

    enter image description here