To start, there are three sets of a large amount of data on three different sheets. There are multiple columns for each row of data. Each row of data has a natural account number. For each natural account number, there is a separate blank sheet. I need these rows of data to populate on the correct sheet based upon the natural account number. Please let me know if this is not clear. Thank you!

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 formulaI 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.

It is not clear and would be much better if you included a sample workbook (not private data). If you can't attach it here then share it using Sharepoint, Onedrive or the like.That said I will take a stab at it and say:a) include the 'natural account number somewhere (e.g. A1)Then include a formula like=VSTACK(FILTER(Sheet1!A1:Z10000,Sheet1!A1:A10000=A1),FILTER(Sheet2!A1:Z10000,Sheet2!A1:A10000=A1),... )Adjust ranges and cells accordingly and this does assume you are using Excel 365

mtarler I sent you a dm with the link to the example workbook

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,""))

Data Separation on Different Tabs | Microsoft Community Hub

14 Replies

mtarler
Silver Contributor
Jun 13, 2023
It is not clear and would be much better if you included a sample workbook (not private data). If you can't attach it here then share it using Sharepoint, Onedrive or the like.
That said I will take a stab at it and say:
a) include the 'natural account number somewhere (e.g. A1)
Then include a formula like
=VSTACK(
FILTER(Sheet1!A1:Z10000,Sheet1!A1:A10000=A1),
FILTER(Sheet2!A1:Z10000,Sheet2!A1:A10000=A1),
... )
Adjust ranges and cells accordingly and this does assume you are using Excel 365
- gvoisard
  Copper Contributor
  Jun 14, 2023
  mtarler
  
  I sent you a dm with the link to the example workbook
  - mtarler
    Silver Contributor
    Jun 14, 2023
    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.

Forum Discussion

Data Separation on Different Tabs

14 Replies

Resources