Forum Discussion
VSTACK, INDIRECT, and BYROW to pull in data from many sheets referencing a range of cell addresses
(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_1 | column_2 | column_3 | column_4 |
| Sales | US | 3/4/2022 | 546.34 |
| Sales | DE | 1/3/2022 | 324.11 |
and in another sheet I may have this data:
| column_1 | column_2 | column_3 | column_4 |
| OpEx | US | 1/2/20 | 114.56 |
| Opex | DE | 6/7/22 | 84.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:
| address | column_1 | column_2 | column_3 | column_4 |
| sheet1 | A8 | B8 | C8 | D8 |
| sheet2 | A8 | B8 | C8 | D8 |
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 data | column_1 | column_2 | column_3 | column_4 |
| sheet1 | A9# | B9# | C9# | D9# |
| sheet2 | A9# | 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)
| A | B | C | D | |
| 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) |
| 3 | Sales | US | 3/4/22 | 546.34 |
| 4 | Sales | DE | 1/3/22 | 324.11 |
| 5 | OpEx | US | 1/2/20 | 114.56 |
| 6 | OpEx | DE | 6/7/22 | 84.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.
- Hi! Almost - but without the headers.
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'
VSTACK accepts 3D references. You could use:
=VSTACK(TOROW(column_names),data1:data3!B4:E13)- 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.
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!
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.
