0

I am currently extracting data for a research, and I want to automatically copy the data to multiple sheets when a condition is fulfilled, as the data keeps being updated over time. I am aware that this question may be similar to an earlier question, but I believe my question is different because I am trying to copy an entire entry rather than a single data cell, hence the INDEX and MATCH as well as the VLOOKUP functions may not be relevant. Below is the illustration of my data (the actual data consist of tens of columns and thousands of rows):

Dataset

From the data in the Master sheet, I want to filter out studies which investigates 'Infectious disease' and 'Treatment', and copy them to the sheet called Infectious disease x Treatment. I want this to be done for each variable (3 unique Topic entries and 3 unique Focus entries, hence a total of 9 sheets). I want the process to be done automatically, meaning that new inputted data which fulfills the condition will be automatically copied to the corresponding sheet. The result should look like this:

Filtered data from the Master sheet

Filtered data is then copied to the corresponding sheet

Is this technically possible with Excel without the use of VBA? Any help will be appreciated. Thanks in advance

2 Answers 2

0

If you have no issue with the original data continuing to exist on the Master page, then this is very easy to accomplish. However, if you wish it to appear on the sub-sheets and be removed from the Master page, then you must use VBA. Excel is happy to read/copy/present information that exists almost anywhere pretty much in any other place you care to place it but its formulas do not take ANY actions on the materials. They simply look where told to for finding the material to use and give whatever result they were written for in the cell/s they are placed.

So, how? A function called FILTER():

=FILTER('Master sheet'!A2:I19,'Master sheet'!C2:C19="Infectious disease")

This use finds all the rows that have that Topic and present them on the sub-sheet. In this use, you specify the entire range so that you get all the columns of data. For some other use, you might specify a subset of the range. For more complicated needs (say, wanting columns 1,2,3,6,8, and 9 or wanting columns in some other order than on the Master sheet) one can wrap the collecting FILTER() (above) with another and for the criteria give an array constant like {1,1,1,0,0,1,0,1,1} or wrap it with INDEX() instead and use an array constant to specify columns by number (even having them appear more than once).

As to sorting the results for use on the sub-sheets, one can wrap the formula with the SORT() or SORTBY() function.

A drawback, possibly (only you know), is that it is completely dynamic. If an entry is added, that's great, clearly. But entries could also be updated or deleted and there are lots of times either would bad for your intended use. They do not have to be bad, and might be strongly desired, but it's something to consider.

As a side note, since they were mentioned, the problem with using VLOOKUP(), XLOOKUP(), or INDEX/MATCH is NOT the need to return the entire entry, but rather that they will only return ONE such entry, not all of them. For VLOOKUP(), in the third parameter, you'd specify all the columns with an array constant ({1,2,3,4,5,6,7,8,9}) or SEQUENCE(1,9). For XLOOKUP() you just give the full range (and therefore the full list of columns to return) for its third parameter. For INDEX/MATCH you get the row from the MATCH() (or better XMATCH()) and in the INDEX() part, simply use ,, ,0, or the same array constant or SEQUENCE() function from above. Sometimes, if specifying either rows or columns, the other value must be explicitly specified. But specifying a single row does not usually trigger that.

The reason you get only one row back with INDEX/MATCH is because MATCH() will not return an array for INDEX() to use. If one had a way to do so, it would do the trick needed. But FILTER() does it easily, and natively, so why bother?

0

You seem to emphasise the need to copy the data. For clarity, Excel normally* does not copy cells but only references them (except if you manually duplicate with copy and paste, or use VBA to automate the copy and paste process).

You can for instance create a complete clone of a sheet using references.

Such a cloned sheet can be even easier to create in Excel 365 using dynamic arrays and spilling, i.e. a single formula in a cell can return more than one cell as the answer (e.g. enter =Sheet1!A1:D10 in sheet2 cell A1). This is also possible in older excel versions using array formulas (entered using CTRL+SHIFT+ENTER) but is a little less intuitive and flexible. The formulae FILTER and SORT are new Excel 365 dynamic array functions that can also be useful in this case (as in Jeorje's answer).

The biggest benefit of "copying"/cloning via references is that if the original data changes so does the cloned data.

*The only other methods to truely copy/duplicate data semi-automatically is by using pivot tables or references across different workbooks. In both these cases, Excel stores actaul duplicate values in the workbook and requires the user to manually hit the refresh button in order to update the pivot table or linked workbook data from the source.

TL;DR

You cannot have your cake and eat it too :P You cannot copy (make a duplicate that is wholly independent) but at the same time also want the independent duplicate to auto-update itself from the source (which is rather just a formula reference). Pivot tables or linked workbooks are the compromise that requires the user to decide when to update from the source.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .