Mar 15 2023 06:34 PM - edited Mar 16 2023 12:33 AM
(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.
Mar 15 2023 11:30 PM
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.
Mar 16 2023 12:33 AM
@JosWoolley - thanks! That didn't quite work for me. I made an example sheet to illustrate. Would greatly appreciate the help!
Mar 16 2023 12:37 AM
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.
Mar 16 2023 12:56 AM
Mar 16 2023 01:23 AM - edited Mar 16 2023 01:24 AM
You can rename it to whatever you like if you wish.
Mar 17 2023 05:58 AM
VSTACK accepts 3D references. You could use:
=VSTACK(TOROW(column_names),data1:data3!B4:E13)
Mar 17 2023 05:18 PM
Mar 17 2023 06:10 PM
Mar 17 2023 09:23 PM
Mar 17 2023 09:26 PM
Mar 18 2023 01:41 AM
Mar 18 2023 02:57 PM - edited Mar 18 2023 02:58 PM
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'