1

I am using the below formula to concatenate some data into one string:

=TEXTJOIN("",FALSE,ARRAYFORMULA(IF((Data!$F$3:$F=$C2)*(Data!$G$3:$G=$D2),Data!$L$3:$L,"")))

I however have thousands of lines and was looking to put an ArrayFormula in order to have the whole column updated when anything is added. I then switched the formula to the one below but it's not working (added ArrayFormula( at the beginning and :C after $C2):

=ARRAYFORMULA(TEXTJOIN("",FALSE,ARRAYFORMULA(IF((Data!$F$3:$F=$C2:C)*(Data!$G$3:$G=$D2),Data!$L$3:$L,"")))

I first have an EQ error but even if tables are of same size the string returned isn't correct.

Basically:

  1. Is it possible to automate a whole column that already has an ArrayFormula in it?
  2. If so how could I get rid of the EQ error without adding empty lines to my table?

Example: https://docs.google.com/spreadsheets/d/1p9nzTnvbAMjcx3a1puYUBQDNeq5Nzdfsg89gHpBSKGo/edit?usp=sharing

Improve this question
3
  • It is not apparent to me in looking at your first formula what it is supposed to do. Keep in mind that we "out here" can't see what you see. We don't know your spreadsheet, your data or data types, your layout or your end goal. So while what you posted may make perfect sense to you, from "out here," it does not. Consider sharing a link to a spreadsheet that contains at least a minimal amount of realistic data along with your expected result manually entered where you would like to see it. Be sure to set the link's permissions (when creating the link) to "Anyone with the link..." and "Editor."
    – Erik Tyler
    Oct 11, 2021 at 14:42
  • Hi Erik, link posted in the original post
    – Exdefoot
    Oct 11, 2021 at 15:02
  • 2
    There are ways to achieve this with array formulas. However, you are trying to compare 5,500+ rows of data with another 25,000+ rows of data and then concatenate matches for all of those. This would require Sheets to form a virtual grid containing over 140 million Boolean results, exchange them for values and then compress it back to 5500+ rows. In short, it's too much data for Sheets to handle, both due to processing time-outs and character limits (i.e., temporary virtual concatenation before removal of spaces/nulls would exceed the 50k limit per cell).
    – Erik Tyler
    Oct 11, 2021 at 16:29

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.