## Friday, August 16, 2013

### 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

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:
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,