Sunday, September 8, 2013

Google Spreadsheet How can I show data from other sheets combined into 1 sheet

Question:

( by Chuck Whiteley III )


I'm looking for a way to put data from multiple sheets onto one sheet without copy and past.  The reason is because I have to add rows to the sheets and would like the combined sheet to update when I add a row.  Here is a sample doc:

Sheet "ST":

Sheet "SG":
 

As you can see by the example, I have 3 sheets in total.  2 of them have data.  I want the rows from the 2 sheets with data to appear combined on the 3rd sheet.  Of course I would like to leave out the headers if possible.

Thanks for any help you can provide.

Solution:

You will need to install script VMerge from Script Gallery.
Go to menu "Tools" > "Script gallery" and then search for "vmerge" and then install it.

Have a look at the following screenshot of "Combined":




After you have installed the script "VMerge", put the following formula in Cell A2 of "Combined":

=query(vmerge('ST'!A2:H;'SG'!A2:H);"select * where Col1<>'' ")


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.


I also take up private and confidential projects:
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,

3 comments:

  1. This is awesome. The only problem I have is that I added an extra row on the ST sheet, and now it doesn't work. I also tried it on another sheet (the actual project), and it just gives me a "Query completed with an empty output". I'm so sorry to take up any more of your time. This does look like the perfect solution if I can get it to work. I added the VMerge to the sample script and you can probably see the output error.

    ReplyDelete
    Replies
    1. Hi Chuck,
      If you are having numerical values in column A then try the following formula:
      =query(vmerge('ST'!A2:H;'SG'!A2:H);"select * where Col1 is not null ")

      Still if you have issues then I would suggest you to share your spreadsheet with some dummy but realistic data, so that I can have a look at it

      Delete
  2. Perfect. Thank you so much! It works perfectly.

    ReplyDelete