Forum Discussion

gvoisard's avatar
gvoisard
Copper Contributor
Jun 13, 2023

Data Separation on Different Tabs

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 natur...
excel
Formulas and Functions
mtarler's avatar
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's avatar
gvoisard
Copper Contributor
Jun 14, 2023

mtarler 

 

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

  • mtarler's avatar
    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.

    • gvoisard's avatar
      gvoisard
      Copper Contributor
      Jun 15, 2023
      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?
      • mtarler's avatar
        mtarler
        Silver Contributor
        Jun 15, 2023

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

Resources