Return column contents to separate tabs based on text in heading

This works at my end.

Group all summary sheets (Cash through Stock in this case).

In cell A3 of one of those sheets enter this formula filled down and across. It will be applied in all grouped sheets at once.

Formula:

Please Login or Register  to view this content.

Then un-group the sheets.

Thank you very much. This is excellent.

It works very well on the example, but I am really sorry that when I apply it to the main sheet, I realise that my example was incomplete.

There are a couple of extra things that I have realised, and I have attached a new example, with two different output tabs this time ('Cash Book' and 'Cust Acc') for two different types of source data. I guess it might mean two different formulas, and perhaps I should have posted it as two separate threads. Apologies if this is the case.

I basically would like to be able to keep pasting new columns into the input tab, and have them filter into the relevant output tabs, but there are generally two different data layouts I hoped to be able to accommodate. If I had these two types of output formulae covered, I could (with my limited skills), adapt the formulae to each of the different output tabs (I might have 10 or so).

I have given four different sets of data 'entries' in the source data, two of each type, and each separately colour coded. One type is a single column, intended to just be copied across as it is to the output tab, each column beside the last. The other is several columns at once, intended to filter in to a table in the output tabs, beneath the previous entries. The separately coloured sets of data should make it easy to see where I hoped each set of data would end up in the output tabs.

For the 'Cash Book' output tab, it would contain the 'Cash Book' columns copied over from the input tab as they are, one beside the next (with no heading and including empty cells as blanks to preserve spacing of the data). They are red and blue in the example spreadsheet. It would therefore hopefully place each column that appears in the source data to the right of the previous one in the destination. So it would not matter how many rows it copies for each column as there is nothing beneath each column (so it could go down to 200 for example just to cover it). However, it would have to copy over empty cells in the source data (as there are spaces between the rows that I hoped to preserve). It would also not have headings at the top of the column, while the other (Cust Acc) does (and I think that they have to stay this way), so I was hoping that maybe both formulae would be set up to specify which source column it was looking for within the formula itself (in text), rather than referring to a cell (column heading) on the destination tab (as there may not be one). If this was the case, it would allow me to change the formula slightly in each tab. I could then tell it what column it was looking for, where to start reading data from that column, and which cell to start copying the data to in the destination tab.

For the 'Cust Acc' output tab, it is different. Instead of placing each new input column to the right of the previous one, it would hopefully be able to fill the data into one single large table (filling a new row of the same table each time). So new source columns would fill into the output table rows, underneath old ones. This data is green and purple in the example spreadsheet. For this second formula, I have made empty input cells zeros (not blanks), so that it would copy these zeros over but it could know the end of a set of data by stopping at the first empty cell. Then it could continue on in the same column by filling data from the next relevant column on the input column. The columns could maybe be identified by the header, or just all identified as part of the table, then the source columns added in the order that they appear, which will be the same in both the source and the destination.

Thank you so much again for your help.

adapt the formulae to each of the different output tabs (I might have 10 or so).

Will there be any format/layout common to multiple sheets?

so I was hoping that maybe both formulae would be set up to specify which source column it was looking for within the formula itself (in text), rather than referring to a cell (column heading) on the destination tab (as there may not be one). If this was the case, it would allow me to change the formula slightly in each tab.

You are making a lot of work for yourself there.

For what it's worth a formula can be written to return the sheet names in helper cell which can be hidden or set out of sight (off to the far right?).
If there is a way to change sheet names to match relevant column headers this would be far easier and more efficient. It would also be less prone to "buggy" formulas.

That said and noting that this last upload bears no resemblance to the first one I am reluctant to post solutions at this time only to find that they won't work for the rest of the project. There appear to be too many "moving targets" and unknown variables (unstated as of yet).

We usually don't ask for larger samples, but in this case I would want to see something closer to what you will actually be working with ... 10 sheets warts and all. I am keen on finding any way to standardize the job.

Hi,

Thank you for your response, and thanks for your patience. I was attempting to simplify the original post, but I did a terrible job of it. Apologies.

I have attached the full spreadsheet, warts and all, with two days worth of source data in the input tab (6/12/18 and 7/12/18). It includes every output tab required, with the data pasted in manually as I hoped it would appear via the formulae.

I had said ten output tabs as I wanted to have the capability to add more in the future, but as it is there are only 5 output tabs. I have made sure that they are named to match the column headings, as per your suggestion. I like your suggestion about using the sheet names and a helper cell.

I have to apologise for my attempt to find the common denominator, and perhaps my knowledge of the various functions is not enough to say exactly what this is. However, I am fairly convinced that there are only two different formulae needed, one to return the columns of the 'cash book' or 'wages' tabs side by side in those respective tabs, and a second formula for the other three output tabs, which in each case populates the rows of a table in the output tab, one beneath the other.

I really hope that seeing the whole thing doesn't make my attempt to summarise it completely invalid,but perhaps that is the case.

Many thanks again for your help.

Thank you for the feedback. I was off line over the weekend and had other stuff to attend to last couple of days.

I'll have a look.

In the attached please find solution applied to 'Cash Book' tab only. This should be adaptable to 'Wages' as well.

I like to use a helper cell with multiple sheets grouped that returns each of those sheet names in cell A1. With the summary sheets grouped enter this formula in A1.

Formula:

Please Login or Register  to view this content.

Due to sheets grouped it will copy through to all those sheets. You may want to save the formula as text somewhere else so you can use it again when you add more sheets. I say this because the CELL function is volatile. It and its dependents recalculate recalculate at the slightest non relevant editing. It is best to copy A1 (still grouped) and paste values only back to A1. Do this again each time you add new sheets and reapply the formula.


The formula that returns the 'Cash Book' data is an array formula. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter. In C2 filled down and across.

Formula:

Please Login or Register  to view this content.

Thank you very much. This is excellent. It is very impressive. I have applied it to the main project and tried some data, and it is very effective.

I could mark this thread as solved, but my one concern is how volatile the whole spreadsheet seems to be (which you did mention yourself), as every single output cell is an array formula.

I had mentioned in the initial post that I really hoped to avoid having a large number of array formulae. I am a little concerned about the stability when I start adding a lot of data to the input tab. If I wanted to have it set up to handle a lot of columns of data, then it would require a very large number of array cells. As you have also mentioned in your last post, it will be 'recalculating' every time I make a change of any kind.

I think that your approach is very nicely engineered though, and I am really sorry to suggest a change when your solution is so well put together. However, it leaves me wondering about going back to my original suggestion in my first post, which was intended to eliminate a lot of the array formulae. It would basically involve using a single array formula for each output column, which would then act as a helper cell.

I hope you do not see this as me moving the target, and this is why I am referring back to this suggestion in the original post. I had suggested in the first post on the thread, that an array could be employed in one helper cell of each output tab column, just to identify the source columns that have 'cash book' in the title, then a separate (non-array) formula could be used for the main body of the output column, to actually copy across the data (based on the column location provided by the helper cell).

The helper cells could perhaps employ something like the SMALL function, or AGGREGATE (across a single row of the output tab), to return a list from one cell to the next, of the location of each column where cash book appears? It might output the cell reference for the top of the source column. If this list went horizontally, it would then effectively be a helper cell for each column of the output tab.

It would then be a very simple function in each cell of the output column, taking the reference from the helper cell, or the column ID, and copying the data across.

With my (admittedly limited) knowledge, I had imagined that if the helper cell output the reference of the top of the source column (D1 for example), then the formula from the body of the output column could use something like the INDIRECT and OFFSET functions to copy the cells of the column across one by one, but I am sure there is a more sophisticated way of copying the whole column across, once identified appropriately by the helper cell.

Thank you again for your help, and if you consider your answer to have already solved this thread then let me know and I will mark it as such, but hopefully you will not mind considering helping with this possible alternative approach.

I do appreciate (thanks to your feedback), that the project has turned out to be more complicated than I thought when I originally posted it, so I am happy to mark this thread as solved and post the separate parts of the project as separate threads (one at a time), if you think that it would be more in line with forum policy.

If not I am happy to continue with this thread as it is. I am not sure if it is helpful, but there is another example spreadsheet attached showing the very first problem to be solved - how to create the helper cells that identify the 'cash book' columns.

Thank you again for all of your help.

Regards

Perhaps I didn't adequately address the volatility issue. The volatility comes from the CELL function. (INDIRECT and OFFSET are also volatile.) If you would like clarification on volatility try this link.

http://www.decisionmodels.com/calcsecretsi.htm and that's why I suggested copy/paste values only in A1.

The array formulas are not volatile but they are resource heavy. It's another and important issue all by itself. Volatility can exacerbate that as the array formulas are dependent upon cell A1. BTW using AGGREGATE will not make the formulas non array. AGGREGATE usually eliminates the need for Ctrl + Shift + Enter. They are still array.

I don't know of another formula way (avoiding array formulas) here given the layout(s) and volume (growing) of data and mixed data types. Even the revised upload and suggestions don't look like a remedy for array formula.

I've not found a way to handle them with Pivot tables either.

You may need to contact a Moderator and have them move your thread to the VBA section (just as it is). Please do not do this yourself. That would be double posting again.

Sorry I can't be of further help.

Dave