Combine range from multiple sheets into single column in order (top, bottom, left, right)-CodePudding

I have found some great formula's on this website but I can't figure out how to order my values from top to bottom and left to right. I have the following formula:

=TRANSPOSE(SPLIT(ARRAYFORMULA(CONCATENATE(TRANSPOSE(CONCATENATE({(Sheet1!A1:Z100&"-");(Sheet2!A1:Z100&"-")})))),"-"))

It does a great job at filing every value into a single column but it is reading left to right and it's driving me nuts seeing it out of my desired order! Is there a way to restructure this? I have found that the following formula keeps it in the order I want but I can't combine multiple strings with transpose.

=TRANSPOSE(SPLIT(ARRAYFORMULA(CONCATENATE(TRANSPOSE(Sheet1!A1:Z100&"-"))),"-"))

Sheet 2

Result sheet

CodePudding user response：

Try this:

=ArrayFormula({"Header";QUERY({FLATTEN(TRANSPOSE(Sheet1!A:B));FLATTEN(TRANSPOSE(Sheet2!A:B))},"Select * WHERE Col1 Is Not Null")})