Friday, August 16, 2013

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


( 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 NameOrder Number:Pickup DATEPickUp TIMEPurchase 1QtyPurchase 2Qty 2Purchase 3Qty 3Purchase 4Qty 4
Pumpkin PieApple pieCherry Pie
Antino1234511/24/13AMPumpkin Pie1Apple Pie2Cherry Pie1Coconut Custard1
Luis1234611/25/13PMPumpkin Pie1Apple Pie2Cherry Pie1Pecan Pie1
Test1234711/26/13PMApple Pie1Pecan Pie2Pumpkin Pie2Cherry Pie2

Antino1234911/26/13AMPecan Pie1Pumpkin Pie2Coconut Custard1Apple Pie2

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 


Have a look at the following screenshots:



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: 


No comments:

Post a Comment