Google Spreadsheet Find the name of an item and pull the number on the column beside on the right

Question:

( by JGmero )

Hello Everyone,
I am almost bald because of this problem!
We are using the forms to enter pie orders to a spreadsheet but the orders can be several different pies in one column, i need to count each particular pie that can be in 4 columns (see attached)

Employee Name

Order Number:

Pickup DATE

PickUp TIME

Purchase 1

Qty

Purchase 2

Qty 2

Purchase 3

Qty 3

Purchase 4

Qty 4

Pumpkin Pie

Apple pie

Cherry Pie

Antino

12345

11/24/13

AM

Pumpkin Pie

1

Apple Pie

2

Cherry Pie

1

Coconut Custard

1

1

2

1

Luis

12346

11/25/13

PM

Pumpkin Pie

1

Apple Pie

2

Cherry Pie

1

Pecan Pie

1

???

??

??

Test

12347

11/26/13

PM

Apple Pie

1

Pecan Pie

2

Pumpkin Pie

2

Cherry Pie

2

Antino

12349

11/26/13

AM

Pecan Pie

1

Pumpkin Pie

2

Coconut Custard

1

Apple Pie

2

I need to figure out how to search for Pumpkin Pie in the Purchase columns and then get the quantity to the right of the found column.

So search Purchase 1 for Pumpkin Pie in the Purchase1 to Purchase4 columns and if found in any column, give me the total of the Qty1 thru Qty4.
is that possible?
if it doesn't find it - put a Zero or blank
please help,

thanks in advance,
Jorge Garcia 

Solution:

Have a look at the following screenshots:

Sheet1:

Sheet2:

First install script "VMerge" from Script Gallery. Go to "Tools" > "Script Gallery" and then search for VMerge and install it.

I have the following formula in Cell A1:

=query(VMerge('Sheet1'!E2:F;'Sheet1'!G2:H;'Sheet1'!I2:J;'Sheet1'!K2:L);"select Col1,sum(Col2) where Col1<>'' group by Col1 label Col1 'Purchase', sum(Col2) 'Total' ")

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