Forum Discussion
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...
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.
- I have updated ACC125 on that linked sheet (is there any reason you don't include that link here?) to be:
=LET(d,VSTACK(FILTER(Table1,ISNUMBER(XMATCH(Table1[#Headers],$A$1:$D$1))),FILTER(Table2,ISNUMBER(XMATCH(Table2[#Headers],$A$1:$D$1)))),
FILTER(d,INDEX(d,,1)=G1,""))
So in this case I used FILTER on the tables so it could dynamically filter out only the column headers that match the headers on this sheet. If the header names don't exactly match this will not work.- Can you explain what "d" is right after let?