I'm wanting to import whole rows of data from 'SPREADSHEET A' to 'SPREADSHEET B' but only if Column 11 in 'SPREADSHEET A' contains the text 'EXPIRED'.

The data in 'SPREADSHEET A' contains about 12 columns and numerous rows, with each cell containing either text, a number, or are blank.

When each row of data from 'SPREADSHEET A' is transferred, I want the imported information concated across each column for each row, so that it looks like an uninterrupted sentence when it's transferred (Meaning that blank cells are skipped).

Is this possible? Or should I cut my loses?


1 Answer 1


Try this...

=filter(arrayformula(trim(Sheet1!A1:A&" "&Sheet1!B1:B&" "&Sheet1!C1:C&" "&Sheet1!D1:D&" "&Sheet1!E1:E&" "&Sheet1!F1:F&" "&Sheet1!G1:G&" "&Sheet1!H1:H&" "&Sheet1!I1:I&" "&Sheet1!J1:J&" "&Sheet1!K1:K&" "&Sheet1!L1:L)),Sheet1!K1:K="Expired")

Using the filter function in SpreadsheetB by referencing SpreadsheetA is what can bring the data between the two sheets only when Col11 = "Expired"

The concatenate part is a bit manual because you can't use concatenate() or join() with the filter (or arrayformula) function to output the concatenate per row as you requested. So you basically need to manually concatenate each column with the & to allow the arrayformula() to read it correctly.

You mentioned you wanted it to read like a sentence, so I included a space between each cell (this is the &" "&). However, you also said you want to ignore blank cells. This formula will include a blank cell and will look like an extra space in your "sentence" Therefore, I also included the trim() function which will reconcile any leading, trailing, or duplicate spaces between.

Here is a sheet with a sample for you to copy and use. https://docs.google.com/spreadsheets/d/1c7N4bhY2qVetvdvEdbH86hSaT7T0TMUafV8yyuYMmh4/edit?usp=sharing

EDIT: For clarification you may have meant spreadsheet like two different unique google sheets which is why you included importrange() in your question. While you can inject importrange() into each range that you need to concatenate in this formula. That might be a bit much. So my suggestion would be to simply importrange() the whole contents of SpreadsheetA into a sheet in B, and use the above to cut it down to what you need. But I'm not clear if that is what you meant or not.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.