CODEWITHSUNDEEP
google-sheetsarray-formulasgoogle-sheets-formula
Mario Andrés
Mario Andrés

Reputation: 107

Intersperse a column (or row) among many others

Is there any formula to intersperse a column or row among many others? For example, if I have rows A, B, C, D, E and F, let me keep A, B, A, C, A, D, A, E, A, F. I need to do this for a variable number of rows and columns.

Upvotes: 1

Views: 353

Answers (3)

alexamy
alexamy

Reputation: 23

As an alternative to @player0 answer, maybe you'll find it more readable:

={A1; TRANSPOSE(SPLIT(JOIN("♠"&A1&"♠"; A2:A6); "♠"))}

Upvotes: 0

Joel Reid
Joel Reid

Reputation: 993

No. There is no formula to directly achieve that.

You could make another sheet reflect those rows, e.g. set Sheet2 cell A1 to
={Sheet1!1:1;Sheet1!2:2;Sheet1!1:1;Sheet1!3:3;Sheet1!1:1;Sheet1!4:4;Sheet1!1:1;Sheet1!5:5;Sheet1!1:1;Sheet1!6:6}
That formula just grabs and stacks up rows: 1, 2, 1, 3, 1, 4, 1, 5, 1, 6.

If you're just looking to always be able to compare lower rows' values with row 1, you could get lower rows right next to row 1 by freezing row 1 and scrolling the rest. Or by hiding the rows. Freezing and hiding also work for columns.

Finally, you could try your hand at programming it to do that in Google Apps Script. The script could look at how many rows have data, insert a new row after each, and copy row 1 to the new blank rows.

Upvotes: 1

player0
player0

Reputation: 1

sure, why not:

=ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(QUERY("♠"&A1&"♠"&A2:A6, , 999^99), "♠"))))

0

Upvotes: 1

Related Questions