yogi_Workaround For Querying A Field Of Mixed Data Type And Extracting Numeric Values Only

                                           Google Spreadsheet   Post  #842
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Oct 31, 2012
user SagRU said: (http://productforums.google.com/forum/?zx=gdxpen6jynp#!category-topic/docs/spreadsheets/V56zP1Ts-Ys)
Query (only numeric values)
Hello!

Is it possible to make QUERY function copy only numeric values? For example, I have a formula:

=QUERY(B1:E100,”select B").

Let's assume, that column B contains both text and numeric values. I need to get only numeric values while skipping text ones.

Thanks in advance!
----------------------------------------------------------------------------------
following is a solution to the problem

yogi_Pull Data From Sales And Customer Sheets By Dates In User's Report Format

                                           Google Spreadsheet   Post  #841
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Oct 31, 2012
user Stephen Seattle said: (http://productforums.google.com/forum/?zx=gdxpen6jynp#!category-topic/docs/spreadsheets/DnnLepXiVA8)
Multiple sheets and formulas
I just mess up my spreadsheet and can't figure out how to fix it.  And since I'm new to Google Docs, I attempted to fix the problem but failed.  I'm stuck and would greatly appreciate any expert out there who could help me resolve my problem.

The premise: I have a spreadsheet with multiple sheets.  The link to my spreadsheet is below.  In my sheet called 'Press Orders', I am importing data from 2 other sheets ('Sales Data' and 'Customer Database') based on a date that I manually enter in cell A4 on the 'Press Orders' sheet.  The formula I have in A5 (then copied to cells A18, A31, A44, A57), is working perfectly for importing data into columns A, B, F, and G.

But in columns C, D and E, the data isn't importing correctly.  I need the 'Sales Data' columns H and J to be imported into 'Press Orders' columns D and E.  Then for column C, the formula is based on the matching date from A4 (as well as A18, A31, A44, A57) on the 'Press Orders' sheet to the date columns K and M of the 'Sales Data' sheet, I want the # of bottles listed in 'Sales Data' columns L and N to import to column C on the 'Press Orders' sheet for any matching dates.  I need the formula in cell A5 to be corrected to accomplished this.  (I can then manually copy it to the A18, A31, A44, and A57.)

The formula I'm currently using is:

 =ArrayFormula(IFERROR(QUERY(IF({1,1,1,1,1,1,1,0,1};'Sales Data'!A3:J;IF(INT('Sales Data'!K3:K)=A4;'Sales DATA'!L3:L;IF(INT('Sales Data'!M3:M)=A4;'Sales DATA'!N3:N;-1)));"select Col1, Col6, Col9, Col8, Col10 where Col8 > -1")))

The spreadsheet is located at: https://docs.google.com/spreadsheet/ccc?key=0AlzYDMIjDWrqdGJqYlNOR3FJUWZoZjhzMEoyWV9WQWc

Any help with this would be awesome!  Please do not hesitate to ask me any questions.

Thank you!

Stephen

----------------------------------------------------------------------------------------------------

following is a solution to the problem

yogi_Extract The First Instance Of Most Occurring Candiate In Different Columns To Meet Specified Criteria

                                           Google Spreadsheet   Post  #840
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Oct 31, 2012
user Spring79 said: (http://productforums.google.com/forum/?zx=gdxpen6jynp#!category-topic/docs/spreadsheets/PNwQUW8C-2U)
Need help counting occurences

I'm making a google spreadsheet with some statistics and need some help couting some results. Theres 3 things I want to do. I will show an example:

ex.

Col1 Col2 Col3
1978  A  Mike
1991  A  Pete
1978  B  Mike
1984  C  Bob
1985  B  Brad
1978  A  Tom

Count how many different occurences in Col2 where C1= 1978
Result = 2

Count most reoccuring in Col2 where C1= 1978
Result = A

Show which person occurs the most in Col3 where C1= 1978
Result = Mike

Thanks in advance

--------------------------------------------------------------------------------------------

following is a solution to the problem

yogi_Extract Top 3 Names With The Smallest Number Of Days

                                           Google Spreadsheet   Post  #839
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Oct 31, 2012
user wmpwi said: (http://productforums.google.com/forum/?zx=gdxpen6jynp#!category-topic/docs/spreadsheets/lrpdzAc7J04)
Looking up and reporting from separate columns

I would like to report elsewhere in the sheet the top 3 names with the smallest number of days sorted by days.

Thanks.

---

this is the link: https://docs.google.com/spreadsheet/ccc?key=0AiXMAzqhMNqIdGNpOGM5WENGR0s2UnpNaG50MTRTQnc#gid=0

---------------------------------------------------------------------------------------

following is a solution to the problem

yogi_Convert MultiLine Input Within A Cell To A SingleLine Text

                                           Google Spreadsheet   Post  #838
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Oct 30, 2012
user david.vlk said: (http://productforums.google.com/forum/?zx=gdxpen6jynp#!mydiscussions/docs/uTsVxJQ5QkU)
remove enter (end of line) 
In Spreadsheet column with no line braking set I want to remove enters (end of line) or what is called hidden sign so the line is just one line not more. To put the question simple: how to make a "one line" line out of a line which has more lines when "no line brakes" is on? Example:

Bad 
text
text
text

What I want
text text text

I understand that this can be done by manually removing (backspacing for example) spaces at the end of the word on the upper line but how to this automatically in a whole cell? Using delete formatting does not help nor anything else i tried.

Thank you for your suggestions.
-----------------------------------------------------------------------------------------
following is a solution to the problem

yogi_Perform Computations Based On Multiple Criteria From Data In A Number Of Sheets In The Spreadsheet

                                           Google Spreadsheet   Post  #837
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Oct 29, 2012
user beanies said: (http://productforums.google.com/forum/?zx=g9a265uola4#!category-topic/docs/spreadsheets/mNLepVOURJI)
Google spreadsheet help needed. How to search multiple sheet and sum the result based on the critieria

The data is at here

https://docs.google.com/spreadsheet/ccc?key=0AhuKBdnnL_ggdGdFLVZXYVBFNzE4eTQxZWdKdUNYOXc#gid=0

It is a sample of what the data look like. say like I have three person here( the actual data may have more than 3 ppl), and each one has their own sheet to record their own inventory. Each day, if person i bought sth with unit x, he will record it in his own sheet( in the light pink back ground) in the form as" name      units     did he eat it or not" 

For example ,the first record of AAA tells us AAA bought 3 apples and he eat them all.

As he may buy the same thing in different day. Then I use UNIQUE function to find unique items in COL A6, then use FILTER function to find the corressponding total units

My problem is:

1.

I searched online and find how to show up unique col in "inventory"sheet" among different sheet( From AAA, BBB, and CCC)

but I don't know how to use  filter function to search all over the spreadsheet . Can someone help me with this?

2, In the inventory sheet. How can I make the col B-D updated automatically when col A updated a new cell?

----------------------------------------------------------------------------------

following is a solution to the proble

yogi_Create User Specified Reports By Month And Year And For This Week Last Week And Week Before Last Week

                                           Google Spreadsheet   Post  #836
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Oct 29, 2012
user mojo_247 said: (http://productforums.google.com/forum/?zx=g9a265uola4#!category-topic/docs/spreadsheets/Py3sTHEixXY)
need help with creating a report from table
Hi 

I'm using a large spreadsheet, off which I need to generate various report.

Following is a very small sample of the data and the reports I need to generate.

https://docs.google.com/spreadsheet/ccc?key=0AgQAZzfYzo1MdDdWaTBpSUdOc08xNzJkMDdiUkZYR3c

Could I please get some assistance with this.

TIA

------------------------------------------------------------------------------------

following is a solution to the problem

yogi_Workaround For Highlighting All Cells in Column B That Occur More than Once

                                           Google Spreadsheet   Post  #835
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Oct 27, 2012
user Martin Cahill said: (http://productforums.google.com/forum/#!category-topic/docs/how-do-i/CjfiAKDjh2Q)
Duplicate cells
Im using google docs spreadsheet.

In coloumn B

If a duplicate value is entered  anywhere in the coloumn I would like both cells to be highlighted.

Can this be done?

Martin
----------------------------------------------------------------------------------------------------------
following is a Workaround solution to the problem

yogi_MultiCriteria Count With Date Being Blank Or NotBlank

                                           Google Spreadsheet   Post  #834
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Oct 27, 2012
user robjwill said: (http://productforums.google.com/forum/#!category-topic/docs/how-do-i/Iz-9Wx4vadM)
Countifs equivalent for Google Spreadsheet not working
I am trying to 

implement a Countifs equivalent in a Google spreadsheet.

Problem 1:
Range 1 = Locations, Criteria1 = C2:C, Range 2 = Dates, Criteria2 = "".

So I am trying to count locations for each name in cols C2:C that have a blank date value in Dates range. (Note, the dates are imported from another spreadsheet using importrange, formatted as m/d/yyyy, - if that matters...)

I tried, arrayformula(sum(Locations=C2:C)*(Dates="")), but it just leaves blank values (--)

Problem 2:
Then, I need to do the exact same think, but for dates that are not blank.

Thanks!

-------------------------------------------------------------------------------------------------

following is a solution to the problem

yogi_Pull Values From Info1 And info2 Sheets Into Results Sheet For Specified Fields

                                           Google Spreadsheet   Post  #833
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Oct 27, 2012
user a2b said: (http://productforums.google.com/forum/?zx=7nbhfu4macmk#!category-topic/docs/spreadsheets/L31smuqVXqE)
merging sheets
Chrome browser

Mac OS

I have a file (see link)

https://docs.google.com/spreadsheet/ccc?key=0Av87k6S9hZaqdF85akVUOHRhWG5TMmpCTnF5MkZWMFE

with 2 sheets, each has different info about students

The only shared info is the student name

How do I put his data togethor on a third sheet, importing all relevant info per student name

thank you

-----------------------------------------------------------------------------------------

following is a solution based on my understanding from your scan description

yogi_List The Last Number Of Specified Calls And Compute The Duration Of Calls In Minutes

                                           Google Spreadsheet   Post  #832
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Oct 27, 2012
user RamGopalVerma said: (http://productforums.google.com/forum/?zx=7nbhfu4macmk#!category-topic/docs/spreadsheets/rfhe5OILlbs)
Last 3 Modified Dates 
Hi,

I have a small calling set up , trying to make them follow a process for better qality control.

I need to have a dashboard to check what ar ethe last 3 calling times by the exeutives and the time difference between the calls.

here is the spread sheet i am taking about:

https://docs.google.com/spreadsheet/ccc?key=0AozeEixGmkP1dDRHZk1BQ1R2YUh0Sll4RUFPaERkOVE

Please advice how can this be done.

Nitin

--------------------------------------------------------------------------------------------------

following is a solution to a bit more generalized problem

yogi_Sum 5 Weeks Of Scores And Total Of All Data By Team

                                           Google Spreadsheet   Post  #831
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Oct 26, 2012
user tony.cc said: (http://productforums.google.com/forum/?zx=r3nme9qg99cb#!category-topic/docs/spreadsheets/AmOUfc_cXIo)
sum function
I am trying to enter a sum function  into a league standings sheet with totals from sheets week 1 c6,week 2 c9,week3 c12,week 4 c15,week 5 c18.

week 1 12 points  =12 points

week 2  8  points  =20 points.

week 3 14 points  =34 points.

week 4 16 points  =50 points.

week 5 22 points  =72 points.

So the question is can i enter into sheet 6 the sum value from the other 5 sheets on a weekly basis. 

---

https://docs.google.com/spreadsheet/ccc?key=0An0cXkWSFeKIdHRrLS1INUlKc0pTWE52NGQ0aG9YZEE#gid=0

-------------------------------------------------------------------------------------------------

following is a solution to the problem based on my understanding of tony.cc's requirements

yogi_Compute Running Total Of Inventory By IN OUT And Make Adjustment For Periodic Reconciliation

                                           Google Spreadsheet   Post  #830
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Oct 26, 2012
user Tim Hutton said: (http://productforums.google.com/forum/?zx=r3nme9qg99cb#!mydiscussions/docs/3W-wiOcLKtI)
Trying to create a "Stock Count" feature on an inventory spreadsheet.
We operate a Ink cartridge refill business and I am in the process of creating an inventory spreadsheet to keep a running total of what stock we have, in this case empty ink cartridges.
So far the spreadsheet calculates what stock comes in (empty cartridges) and what stock goes out (filled cartridges and failed cartridges) from a google form and displays the total in stock in a front-end table.

For example:

=if(Sheet2!$C2="Failed Cartridges Out",-sheet2!D2, if(Sheet2!$C2="Pre-Filled Cartridges Out",-sheet2!D2, if(Sheet2!$C2="Empty Stock In",sheet2!D2)))

What I would like to have is a stock count feature where one can simply count up the current stock of an item, input that figure into the form and the spreadsheet adjusts on the front-end table. One could simply have the user count the stock, refer to the table and input the difference making the formulae required simple, but that would defeat the point of having a spreadsheet to save the user time and effort.

At first glance it seems that when Sheet2!=$C2="Stock Count" the spreadsheet calculates the difference between the current total and the stock count total and inputs the difference as a correction. So that the front-end table sums up in the normal fashion.
Unfortunately with my limited knowledge of spreadsheets in general I always end up with a cyclic error, it may be I'm missing out on a trick somewhere or I need a script to do what I need or simply I'm looking at it all from the wrong way round.

Any help would be appreciated, I'm on a learning curve here.

Link to file:

https://docs.google.com/spreadsheet/ccc?key=0ArfJ2GoGyVLddGtsQlUwclhTNi1kZDZ5WUNCSHhHWXc

-----------------------------------------------------------------------------------------------------------

following is a solution to the problem

yogi_Make Computed Columns For User Specified Computations Over Many Columns

                                           Google Spreadsheet   Post  #829
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Oct 26, 2012
user Roi RRR said: (http://productforums.google.com/forum/?zx=r3nme9qg99cb#!category-topic/docs/spreadsheets/pDb-riZoDLA)

Generating the same formula with every new row 
I have a quality assurance questioner were the answer can be either yes or now  

When a form transfers data to its spreadsheet, it adds a new row but does not use the formula that I put in the previous record row.

I would like to add a new COUNTIF formula for each new row populated by the form in order to count the Yes's on every row.

---

I redid the sheet on my personal google account in order to provide you guys with permissions:

https://docs.google.com/spreadsheet/ccc?key=0AlKmvcLrAW2OdGJnN293d2tiTWZjS1R5S0dKcFVEQmc

---------------------------------------------------------------
following is a solution to the problem

yogi_Identify Last Row Of A Non-Empty Row For each Column Of A Table

                                           Google Spreadsheet   Post  #828
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Oct 26, 2012
user esmithnola said: (http://productforums.google.com/forum/?zx=r3nme9qg99cb#!category-topic/docs/spreadsheets/GBML1XItLHw)
Function to identify last row of a column that's not empty
I've got a sheet like:

Date              A          B         C

2012-10-24     x                      x

2012-10-25                  x         x

2012-10-26     x

I'd like a way to determine the last date for each of A/B/C columns that's checked, e.g.:

                  10-26     10-25   10-25

I think if I could get the row number, I could figure out how to get the data.

Any suggestions?

-Dave

----------------------------------------------------------------------------------------------------

following is a solution to the problem

yogi_Query Sheet1 For User Specified Criteria And Present Resulting Table In Sheet2

                                           Google Spreadsheet   Post  #827
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Oct 25, 2012
user esmithnola said: (http://productforums.google.com/forum/?zx=r3nme9qg99cb#!category-topic/docs/spreadsheets/fN5cQZeLs24)
Query returning all matches in a single cell, please help!
If you want to see my sheet and formulas, you can access/edit at 

https://docs.google.com/spreadsheet/ccc?key=0AkwOFG9G269gdEcxR2pObl9MZGxFMC1pR3ZwYnZGRlE

I've written a query where my Sheet2 goes to look in Sheet1 and returns columns A, B, AV, CC when column AH or AJ match a value input in B3 on Sheet 2

=QUERY('Sheet1'!A:CC;"select A,B,AV,CC where C=11 and (AH <='"&$B$3&"' or AJ <='"&$B$3&"') order by B")

It works, but it returns all the values in a single cell instead of a new row for each match (normal behavior). Any thoughts? I've tried all sorts of things, but still the same behavior.

(Btw, in case you're wondering I'm trying to make a sheet where I can automatically pull certain students based on input criteria. All the information on those sheets is just made up.)

--------------------------------------------------------------------------------------------------

following is a solution to the problem