如何在Google Sheets中用公式将两列合并为单列？

Hey there! Let’s figure out how to merge those two columns into a single alternating column in Google Sheets—going from Column 1 (1, 3) and Column 2 (2, 4) to a single column with 1, 2, 3, 4. Here are a few reliable formula options depending on your needs:

Method 1: Flexible Formula for Any Number of Rows #

If you have a dynamic dataset that might grow, this formula works great. Assuming your data starts in A1 (Column 1) and B1 (Column 2):

=INDEX({A:A; B:B}, SEQUENCE(COUNTA(A:A)*2, 1, 1, 1/2))

How it works: #

• {A:A; B:B} first stacks Column A on top of Column B (so you’d get 1, 3, 2, 4 initially).
• SEQUENCE(COUNTA(A:A)*2, 1, 1, 1/2) generates a sequence like 1, 1.5, 2, 2.5...—when fed into INDEX, this picks the first value from Column A, first from Column B, second from Column A, second from Column B, and so on.

Method 2: Clean, Structured Formula with FLATTEN #

This one’s more readable if you prefer explicit structure. It builds a 2-column array of paired values then flattens it into a single column:

=FLATTEN(MAKEARRAY(COUNTA(A:A), 2, LAMBDA(r,c, IF(c=1, A[r], B[r]))))

How it works: #

• MAKEARRAY creates a table where each row has the matching value from Column A and Column B (like [1,2], [3,4]).
• FLATTEN turns this 2D table into a single column, resulting in the alternating order you want.

Method 3: Quick Manual Array (For Small, Fixed Datasets) #

If your data has a set number of rows (like just 2 rows in your example), you can hardcode the range for simplicity:

={A1; B1; A2; B2}

This is super straightforward—just list each cell in the order you want, separated by semicolons to stack them vertically.

Pro Tip: #

If your columns have blank rows, replace A:A with your exact data range (e.g., A1:A10) or keep COUNTA(A:A) to only count non-blank rows and avoid extra blanks in the result.

内容的提问来源于stack exchange，提问作者sheetsquestions