I’ve got an answer that doesn’t require coding.
- I’ll assume that you want to put the combined (concatenated) list
D of the same sheet.
If you want it elsewhere, that’s a trivial change.
- My solution requires two “helper columns”;
for example, Columns
F on the same sheet.
If you want them elsewhere, that’s a trivial change.
- I assume that you will not be putting blanks
in the first three columns (i.e., in the data, before the last value),
and that you want those columns simply concatenated, and not sorted or deduped.
If I misunderstand, that’s a major change.
- I assume that every column has at least one value;
C1 are non-blank.
If that assumption is not valid,
this solution can (probably) be adapted for that.
=IF(E1<0, "", OFFSET($A$1, F1, E1)) into cell
- Drag/fill cell
D1 down into
0 into cells
=IF(E1<0, E1, IF(OFFSET($A$1, F1+1, E1)<>"", E1, IF(E1<2, E1+1, -1)))
=IF(E1<0, 0, IF(OFFSET($A$1, F1+1, E1)<>"", F1+1, 0)) into cell
- Select cells
D2:F2 and drag/fill down as far
as you expect the combined list ever to be.
(Of course you can always extend it later.)
the (0-based) column and row number of the value in Column
A negative value in Column
E indicates that
you are beyond the end of the data.
The formula in Column
D evaluates to blank if the
E value is negative;
otherwise, it uses the
OFFSET() function to retrieve
the value indexed by the
The formulas in Columns
F check whether
E value is negative,
and, if it is, set the next row to
so, once you’re past the end of the list, you’re past it,
and you don’t start tying yourself into knots.
Otherwise, if there is a next value in the current column,
we advance to that value (
Otherwise, we go to the top of the next column —
except, if the current column is #2 (
we advance to column #-1, because we’re at the end.
You can now add and delete values in Columns
and the list in Column
D will update automatically.
Once you get this working, you can hide the helper columns.