Forum Discussion
Data Separation on Different Tabs
gvoisard In there I gave 3 different options for the 3 different accounts. In each case I added the account name in cell G1:
using regular range references (not recommended as best option):
=VSTACK(
FILTER(Data!A2:D50,Data!A2:A50=G1,""),
FILTER('Data (2)'!A2:D50,'Data (2)'!A2:A50=G1,""))in this case i used normal range reference and arbitrarily selected to go to A50 but if the data grows past that you have to update the formula
I formatted the 2 data tables as tables (home -> format as table). i didn't change the table names but would recommend that too:
=VSTACK(
FILTER(Table1,Table1[Natural Account]=G1),
FILTER(Table2,Table2[Natural Account]=G1))and then in the 3rd option I just stacked the tables first and then did 1 Filter:
=LET(d,VSTACK(Table1,Table2),
FILTER(d,INDEX(d,,1)=G1,""))
That all said, I don't recommend this approach at all. I suspect you may have dozens or hundreds of accounts so why not just combine that data into ONE table and just use the built in filters to look at 1 account or another? OR using ANY of the above solutions you can just change cell G1 to see a different account, OR you can use a PivotTable and use the filter box or a slicer to show 1 account or multiple. Depending on what exactly you need it for will depend on which approach might be better or any of those approaches may be fine.
I hope this helps.
- Thank you! I think that VSTACK seems to be the way to go. The only issue that I face is that each data sheet has different headers. Data 2 sheet also has more headers than the data sheet. So, the issue is that when the VSTACK formula is used on the natural account sheets, the data is not in the correct columns and in some cases spills over. Is there a way for the data to only be entered if it matches the headers on the natural account tabs?
gvoisard yes you can use CHOOSECOLS to pull only the correct cols of data. do you have headers on the table/sheet you are pulling to? Do those headers match? You could do it manually. It is really hard to do this 'blind'. If you want to just manually do it you can just add the CHOOSECOLS so :
=LET(d,VSTACK( CHOOSECOLS(Table1,1,2,4,6,7) , CHOOSECOLS(Table2,1,2,3,5,7)), FILTER(d,INDEX(d,,1)=G1,""))- Please refer to the excel sheet that I sent to you yesterday via DM. I have updated the Data Sheet by adding a few columns. Please note that I only want Natural Account, Item, Date, AMT on the separate Natural Account Sheets. Somehow, I need the new columns to be "hidden" when the data transfers to its respective sheet based on the natural account. Thank you.