[SOLVED] Why does Excel return multiple columns of data combined from several sheets in a single workbook?

Why does Excel return multiple columns of data combined from several sheets in a single workbook?

I have a formula which uses VSTACK to combine similar data from several sheets in Column A, and a separate VSTACK that combines the data in Column B. I am trying to return both of these columns side-by-side in a new sheet using a single formula. I feel like the solution must be simple, but I can't seem to figure it out.

Here is the formula I am using:

=LET(
    a,VSTACK(Sheet1!A1:A100,Sheet2!A1:A230,Sheet3!A1:A103, 
    b,VSTACK(Sheet1!A1:A100,Sheet2!A1:A230,Sheet3!A1:A103), 
    FILTER(a,a <>"")
)

There are some blank rows here and there, which is why I used the filter function. I was hoping to return the two VSTACK arrays side by side. How can I accomplish this?

Solution

You need to add HSTACK to your formula:

=LET(
    a, VSTACK(Sheet1!A1:A100, Sheet2!A1:A230, Sheet3!A1:A103),
    b, VSTACK(Sheet1!B1:B100, Sheet2!B1:B230, Sheet3!B1:B103),
    filtered_a, FILTER(a, a <> ""),
    filtered_b, FILTER(b, b <> ""),
    HSTACK(filtered_a, filtered_b)
)