Array Formula creating more rows than it should

Hi, i created an arrayformula that works, but it creates more rows than it should, pls see this screen.

The blank on the left shouldn’t create any rows on the right.

TIA.

You need another IF around your VLOOKUP to only put in a value of A1:A <> “”.

Hi Jeff, Thanks but my formula building skills are still noobish. I tried adding them like this:

=arrayformula(IF(row(A:A)=1,“Codes Used”, IF(VLOOKUP(A1:A,Usage!A2:A,1,false)))

But does not work. Could you point out exactly where do I put it?

What exactly you are trying to achieve?

Just simply use the function = iferror(-; » ») wrapped around your array formula
Replace the - by your array formula

Thanks but this problem leads to the the same, minus the N/A, which I use in pivot table elsewhere.

This is a promo code dispenser.

User sign up and get a code sent to their email.

The codes are taken from another sheet, pre-randomised.

The app also checks for codes that have been used. The purpose of this is to check against promo codes issued against redeemed, to have un-used codes.

While this existing formula works, it generates additional rows which I don’t need. The extras are the ones I wish to have not generated.

=ARRAYFORMULA(IF(ROW(A:A)=1,"Codes Used", IF(LEN(A1:A)=0, "", VLOOKUP(A1:A,Usage!A2:A,1,false))))

Hi Jeff, Thanks, the code does work a little better, but still generates blank fields at the bottom. This might be the best workaround as I dont want to hit the row limit of Glide.

The last column on the right is using your formula.

here’s the link if anyone wants to take a look under the hood:

When using arrayformulas, you will always have to delete the empty rows. Glide won’t seem them now that they are blank, but if you add new rows through the app, they will be placed at the bottom of the sheet.

Here’s a tutorial on it’s use:

And just my suggestion, unless you have a specific reason for performing the formula in the sheet…you could easily duplicate this same thing with a Relation and Lookup in the Glide data editor. It would be much faster as you wouldn’t have to wait for formulas to run in the sheet, which causes delays.

i’ll take a read, thanks!

1 Like

FYI, I just tested with the formula you gave, it doesn’t add new rows at the bottom, instead continues from my last.

OK. If it works, that’s fine. It’s been a very very common issue for people to say the sheet isn’t syncing with glide only to find out they are using arrayformulas and their new rows are being placed at the bottom of the sheet. This is due to google not wanting to touch existing rows that have a formula being applied to it, although the value may be blank. That’s why it’s suggested to delete empty rows. If you run into it in the future, now you’ll know why.

1 Like