Thursday, September 27, 2012

Merging data from different Spreadsheets into a Master Spreadsheet

Question:

Hi,

I have about 15 spreadsheets that I need to take certain bits of information from and create a master sheet, I have an example spreadsheet here: 



What I am trying to do is take only the rows with the initials LD, AD and JL in the 'Who' column. I need to do this for about 15 different spreadsheets.

Any help would be great :)


Solution:

I assume, you must be having same number of columns in all spreadsheets that you want to merge and filter data from.

Here in this example, I am considering just three spreadsheets:

Spreadsheet 1:



Spreadsheet 2:



Spreadsheet 3:




Now, put the following formula in your master spreadsheet which will fetch records from all the spreadsheets:
=query(VMerge(ImportRange("0AmMTqpzD9YRndHdhaU90Y2tHSmRCaUExZzVuSXdIcGc";"Work list!A4:M");ImportRange("0AmMTqpzD9YRndEtXcTVGd1NYdlBZLUhIQUtsUFdQcHc";"Work list!A4:M");ImportRange("0AmMTqpzD9YRndDZ0MWZ6R1BXd1c0MXRDSm1xajRmRUE";"Work list!A4:M"));"select * where Col10 = 'AD' or Col10 = 'LD' or Col10 = 'JL'";0)

Note: The above formula contains spreadsheet keys in ImportRange formula, if you are not familiar with ImportRange then have a look at the following link:


Here is screenshot of the Master Spreadsheet:



You can notice master spreadsheet has all the records from three spreadsheets which are having "JL" and "AD" and "LD" in Column J.


I hope the above solution will help you, and if you need more help then please do comment below on this blog itself I will try to help you out.

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,
Kishan,

1 comment:

  1. Hi, Thank you for your help. I have tried to use this formula however it comes up with the following error:
    error: Invalid query: Column [Col10] does not exist in table.
    It is probably just something I am doing wrong.
    Any help would be great :)

    ReplyDelete