[SOLVED] EXCEL return multiple columns of data combined from several sheets in single workbook

EXCEL return multiple columns of data combined from several sheets in 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. Any ideas how to accomplish this?

Solution

It's not quite clear what you want to do and you have not provided any examples of your inputs and outputs (How do I ask a good question?) but I'm guessing you probably 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)
)