How To Stop #Value An array value could not be found.

[Johan Horak]

Hi

I send data by scraping emails I receive into a sheet.

1. At the sheet, the raw parsed data is dumped in a new row at the "parse" sheet.
2. In a linked sheet a few Vlookup and other calculations are done.
3. A "final" sheet row is referenced from the "calc" sheet using formulas like =if('Input Parse'!L11="","",'Input Parse'!L11)
4. The =if x="","", is added to stop errors from populating in the not yet rows waiting for the web-hook.
5. For some or other reason, I get this error when the web-hook executes #Value An array value could not be found. 
6. Email Merge Utility checks a new row in the "final". If it appears it checks for a "date" column. If the date column is also blank it emails the data. But if the error populate the sheet 1500 emails are sent :(
   
7. Interesting: when I copy-paste similar info into the "parse" sheet I don't get the #Value error.

Any ideas on how to do this better would be appreciated. 

BTW: I thought of using CopyDown but it depends on data added via a form.

[Joseph Schmidt]

Are steps 3 and 4 in the same formula?  It seems like they should be.

It is always iffy to have a formula in rows that should be empty.  What happens if you just delete the empty rows?

I prefer to use arrayformula instead of CopyDown.  It works even if a form is not used.

Here is a video Arrayformula

.

Joe Schmidt

 GLUA...@gmail.com

New Visions Blog Entry - Joe Schmidt

--
You received this message because you are subscribed to the Google Groups "NV Form Mule Add-on" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nv-form-mule-ad...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/nv-form-mule-add-on/26210429-0e49-49fc-bb09-9152ba3fbd41n%40googlegroups.com.

[Johan Horak]

Thanks, Joe

Very kind of you. The Expert.

Steps 3 and 4 is the same.

I don't yet understand arrayformula as well as I should but it looks like the solution.

I will be able to remove those "hidden" formulas. 

And not having to use CopyDown is good news as, I also, had issues with it.

The way I understand arrayformula is that it will populate a new row (column by column) as new scrapped is dumped by the web-hook.

Let me try.

Thanks

Johan

[Johan Horak]

Hi Joe

Thanks. I simplified it and could make it work. ARRAYFORMULA is great. 

[Joseph Schmidt]

Johan,

I thought you might like arrayformulas.  I usually delete the empty rows so I don't have to include a check for the presence of data.

Your project has really become suffocated. 

Joe Schmidt

 GLUA...@gmail.com

New Visions Blog Entry - Joe Schmidt

To view this discussion on the web visit https://groups.google.com/d/msgid/nv-form-mule-add-on/ebcbd2f2-d23b-493c-93ad-a7a7e7e3b5e1n%40googlegroups.com.

[Johan Horak]

Hi Joe

Thanks. A pleasure to learn from you. I have a "calc" sheet where I do a few calculations before I pull it into the Form Mule sheet where everything is arrayformula.

When you mention "suffocated" I thought I must improve two formulas but the arrayformula seems complicated with these types:

1. =if(C13="","",(VLOOKUP(B13,'VLookup home details'!A$1:G1005,5,false)))
   
2. =INDEX('VLookup home details'!$A$2:$I$1000,match($B12,'VLookup home details'!$A$2:$A$1000,),2)
   

Then I can delete the empty rows as you suggest - and I saw you did in your video. 

Thanks again.

Johan

[Joseph Schmidt]

Johan,

The arrayformula looks scary but I think it is worth learning.

Sometimes you have to deal with blank rows because Google likes to add extra rows.

=if(C13="","",(VLOOKUP(B13,'VLookup home details'!A$1:G1005,5,false)))

Becomes

=arrayformula(if(C2:C="","",(VLOOKUP(B2:B,'VLookup home details'!A$1:G1005,5,false))))

Another interesting item is being able to create an array with squiggly brackets.

={"column title";arrayformula(if(C2:C="","",(VLOOKUP(B2:B,'VLookup home details'!A$1:G1005,5,false))))}

I guess arrayformulas do look scary but I think they are worth it.

Also, some functions cannot be used in an arrayformula. The concatenate function is one of them You have to use the & symbol to join text fields.

Joe Schmidt

 GLUA...@gmail.com

New Visions Blog Entry - Joe Schmidt

To view this discussion on the web visit https://groups.google.com/d/msgid/nv-form-mule-add-on/5d181bb0-e678-437a-a105-6256e25d901dn%40googlegroups.com.

[Johan Horak]

Hi Joe

Thanks. I'll investigate and try your ways tomorrow. 

[Kimberly Reaume]

You can combine the if and vlookup all together as a nested formula and include an iferror so its all on one column.

Kim Reaume
Costco Freight/Payments Manager

Sent by my Galaxy. Please excuse any typos or errors.

To view this discussion on the web visit https://groups.google.com/d/msgid/nv-form-mule-add-on/f53cf0f5-14b2-403b-9401-b6a0b157d721n%40googlegroups.com.

[Johan Horak]

Thanks. 

[Johan Horak]

Hi Joe

Man OH! Man!

That did it. Love it. And you even knew I had a concatenate function as well :) 

This & symbol is so easy to use.

The sheet picked up a bug. I think.

BTW: Using the arrayformula, as you said, is a lot easier than what it looks.

In the last column, I had a concatenate which I now replaced with arrayformula and &.

But oddly enough, an error appears about 1000 rows below saying "Array arguments to CONCAT are of different size". 

But there is no CONCAT left in any row of that column. Even deleting the rows does not fix it. Looks like a bug. 

Here's a gif showing what I mean https://cln.sh/qQAHrx

Thanks for your great support.

Johan

On Sunday, 30 January 2022 at 18:08:53 UTC+2 glua...@gmail.com wrote:

[Joseph Schmidt]

Johan,

I couldn't figure out how to pause the GIF.  I think the problem is that the number of rows you are concatenating is unmatched.

You might have something like =Arrayformula(a2:a&" "b:b)  That would cause one more row of B values than A values.  Make sure your range values are the same number of rows in the arrayformula.  

That also highlights the power of Arrayformula.  Google isn't trying to process the rows one at a time, it does everything in one big operation.

You probably have figured out that I know the solution because I have made the same error.  Let me know if that is not the problem.

I'm also pretty sure you know a different set of tricks that I don't know.  

Joe Schmidt

 GLUA...@gmail.com

New Visions Blog Entry - Joe Schmidt

To view this discussion on the web visit https://groups.google.com/d/msgid/nv-form-mule-add-on/ef8f6483-6a40-4fca-b49c-615d598eee87n%40googlegroups.com.

[Johan Horak]

Hi Joe

Cool.

Here is what I have =arrayformula('Input Parse'!F3:F&H3:H) in that column. I changed it to =arrayformula('Input Parse'!F3:F&H3:H1000) and as you said, the issue is no issue any longer.

Thanks Joe. Glad to have you here helping us out.