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 <> "")))