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