Google Spreadsheet Import data range and select associated cells from same row?

Question:

( by Mr Fitz )

Hi, 

I'd like to know if it is possible to import a range from one sheet to another as well as some but not all of the associated cells from the same row?

I have a master sheet that contains form responses and would like to categorize the data in a more relevant way.

I want to separate out product data and its associated case and bottle values. Is there a way to do this without mixing the different product types up? I've tried a number of importrange and query functions but so far have had no luck! 

Any help would be appreciated.

Thanks.

Solution:

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

To get the above results, first of all install the script "VMerge" from Script Gallery. In your spreadsheet, go to "Tools" > "Script Gallery" and then search for "vmerge" and install it.

And after intalling VMerge, you can have any of the following formula in Cell A1 on "Sheet2":

=query(arrayformula(vmerge(query('Master Bottles'!D2:I;"select D,H,I");query('Master Bottles'!D2:I;"select E,H,I");query('Master Bottles'!D2:I;"select F,H,I")));"select * where Col1<>'' label Col1 'Product', Col2 'Cases', Col3 'Bottles' ";0)

OR:

=query(arrayformula(vmerge(if({1,0,0};'Master Bottles'!D2:D;'Master Bottles'!G2:I);if({1,0,0};'Master Bottles'!E2:E;'Master Bottles'!G2:I);if({1,0,0};'Master Bottles'!F2:F;'Master Bottles'!G2:I)));"select * where Col1<>'' label Col1 'Product', Col2 'Cases', Col3 'Bottles' ";0)

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:

http://igoogledrive.blogspot.com/p/paid-projects.html

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.

iGoogleDrive