I have an Excel 2013 spreadsheet with multiple iterations and several subsets with a variable number of columns dependent on the number of iterations. For each subset of columns only one contains numerical data in a given row or text data in a given row (the other cells in that row are empty)(see attachment - raw data). I want to merge to a single column on a separate worksheet (see attachment - Master sheet). If possible, I would like to include columns contingent upon row 1 heading name(e.g., test, test_1, test_2, or text, text_1), because there are many columns in the spreadsheet with a variable number to be merged. If it were not for the variability in number of columns over time, I realize I could use "sum" for numerical data or "concatenate" for text. however, the variability from one iteration to the next would require rewriting the formulas each time.
any suggestions?

Attached Files

example.xlsx (9.4 KB, 12 views)