VSTACK, INDIRECT, and BYROW to pull in data from many sheets referencing a range of cell addresses

Copper Contributor

(update - attached file)

 

Hi all! 

 

I have a unique problem which is a result of having to create a large "output" range from multiple sheets. I'm building a large "demo data" bank, but the result needs to be one big chunk of data. 

 

Here's how I have it set up: there are many many sheets that contain the same structure of data, but each sheet is different. The columns are also all the same. So in each sheet, there are 9 columns of data, and each column is a dynamic array. For example, in one sheet I may have the following data: 

column_1column_2column_3column_4
SalesUS3/4/2022

546.34

SalesDE1/3/2022324.11 

 

and in another sheet I may have this data: 

column_1column_2column_3column_4
OpExUS1/2/20

114.56

OpexDE6/7/2284.21

The beauty of how I set it up is that all of the columns have the same exact address. So both in Sheet1 and Sheet2, the addresses of the "column_#" are for example: 

addresscolumn_1column_2column_3column_4
sheet1A8B8C8D8
sheet2A8B8C8D8

Because all of the columns of data are dynamic arrays, I can use an indirect reference to pull in the data. So the correct reference of the columns is simply the next row + the "#"

reference to datacolumn_1column_2column_3column_4
sheet1A9#B9#C9#D9#
sheet2A9#B9#C9#D9#

In my big output sheet, I combine all of the columns together using VSTACK and INDIRECT. So (I added a column with the row numbers and a row with column numbers)

 ABCD
1'Sheet1'!A9#'Sheet1'!B9#'Sheet1'!C9#'Sheet1'!D9#
2'Sheet2'!A9#'Sheet2'!B9#'Sheet2'!C9#'Sheet2'!D9#
formula in row 3:=VSTACK(INDIRECT(A1),INDIRECT(A2)=VSTACK(INDIRECT(B1),INDIRECT(B2) =VSTACK(INDIRECT(C1),INDIRECT(C2) =VSTACK(INDIRECT(D1),INDIRECT(D2) 
3SalesUS3/4/22546.34
4SalesDE1/3/22 324.11
5

OpEx

 US1/2/20
 
114.56
6OpExDE6/7/2284.21

This setup is nice because It gives me one nice range that I can then copy into a csv file as "raw data" for sequential processing, but it allows me to manipulate the logic in each sheet as needed. For clarification, the results in each column's rows 3:6 are also one array. 

 

However - there's a big problem. I have over 50 sheets of data. In the formula with the VSTACK above, I have to manually specify each cell with the INDIRECT reference. This is both tedious and disappointing because I can generate the text needed for the indirect reference automatically (simply by listing all sheets, generating the name of the sheets from the sheet number with a tiny bit of VBA, and creating a sequence of addresses with a SEQUENCE since they are all always in the same row and the same column. 

 

My question is - given that the references that lie in A1:C2 is actually one big dynamic array, how does one generate the correct BYROW function to pull those references? 

 

I've tried (repeating this in each "row 3" of the output sheet) =BYROW(A1:A2,LAMBDA(x,VSTACK(INDIRECT(x))))) - but I inherently new that just wouldn't work - and it sure doesn't. 

12 Replies

@tdarenkov 

 

I would imagine there's a much better set-up without all this volatility. Since you're in any case already using some VBA, why not simply add a procedure to perform your required consolidation?

 

That said, here's one possibility:

 

=LET(
    ζ,A1:A2,
    TOCOL(CELL("contents",
        OFFSET(INDIRECT(ζ),SEQUENCE(,ROWS(INDIRECT(TAKE(ζ,1))),0),)
        )
    )
)

 

A1:A2 can be amended as required. And this can of course then be copied to the right.

@JosWoolley - thanks! That didn't quite work for me. I made an example sheet to illustrate. Would greatly appreciate the help! 

@tdarenkov 

 

Can you explain how it "didn't work" for you? I can't see any attempt in that workbook of you trying to implement my solution. I replaced A1:A2 with I6:I8 in the formula I provided and it gave the exact same results as yours in I13:I42.

Apologies. Before trying again - is the "zeta" symbol in your formula above just a placeholder for any letter? Thanks!

You can rename it to whatever you like if you wish.

@tdarenkov 

VSTACK accepts 3D references. You could use:

=VSTACK(TOROW(column_names),data1:data3!B4:E13)
Hi, thanks again! Interestingly enough, it does not want to work if there's a lot of rows and a lot of references. I'm trying to figure out a way to get you a demo worksheet given the sensitivity.
Thanks, but the issue is that there are different amounts of data in each sheet - so "E13" might be "E3000". I guess I could filter it thought?
Yes, stack and filter to clean up the stack. I recommend avoiding INDIRECT, if possible, because of its volatility.

if possible,try this online tool

 

http://e.anyoupin.cn/eh3/merge/allbks2db/merge_with_sht_name.php

select rowid,文件名,表格名,date,divison,amount,account from consolidate_all_sheets where rowid>6 and date!='date'

http://e.anyoupin.cn/bsbm/pdo/eh2/a2502/mybookmark/sql/sql/display_table_t.php?find=select+rowid%2C%...

 

Screenshot_2023-03-19-05-55-04-411_cn.uujian.browser.jpg