I need a formula to concatenate the text from column A with each non-empty cell in B1:Z. The number of rows in the input data will change, so that has to be accounted for dynamically. Also, the rows have varying lengths and may change, so that has to be accounted for dynamically.

Here is sample data (editable by anyone with the link): https://docs.google.com/spreadsheets/d/1isvkle3cWXpiXGxBNsNQYPi3hyyMY0ISwNE3mFt7Cmc/edit?usp=sharing

Given data in a sheet such as InputData I need some type of formula (ArrayFormula, I think) in another sheet that will produce something that looks like what I hard-coded in DesiredResult. The idea is that as data in InputData changes (added, removed, modified), DesiredResult will automatically adjust with no need to edit it.

I showed my best attempt in the sheet Result - Formula Not Correct which uses:

=ArrayFormula(FILTER(InputData!A1:A, InputData!A1:A <> "") & " -> " & ArrayFormula(FILTER(FILTER(InputData!B1:Z, InputData!A1:A <> ""), InputData!B1:Z1 <> "")))

1 Answer 1


Problem solved:

=arrayformula(if(InputData!A:A="","",if(InputData!B:Z="","",InputData!A:A&" -> "&InputData!B:Z)))
  • This is perfect! Thanks so much! Commented Nov 17, 2019 at 15:17

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.