Wednesday, October 31, 2012

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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Oct 31, 2012
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.

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

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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Oct 31, 2012
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")))

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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Oct 31, 2012
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
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
--------------------------------------------------------------------------------------------
following is a solution to the problem

yogi_Extract Top 3 Names With The Smallest Number Of Days

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Oct 31, 2012
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.
---
---------------------------------------------------------------------------------------
following is a solution to the problem

Tuesday, October 30, 2012

yogi_Convert MultiLine Input Within A Cell To A SingleLine Text

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Oct 30, 2012
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:

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.

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

Monday, October 29, 2012

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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Oct 29, 2012
Google spreadsheet help needed. How to search multiple sheet and sum the result based on the critieria

The data is at here

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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Oct 29, 2012
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.

Could I please get some assistance with this.

TIA
------------------------------------------------------------------------------------

following is a solution to the problem

Saturday, October 27, 2012

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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Oct 27, 2012
Duplicate cells

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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Oct 27, 2012
I am trying to

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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Oct 27, 2012
merging sheets
Chrome browser
Mac OS

I have a file (see link)

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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Oct 27, 2012
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.

Nitin
--------------------------------------------------------------------------------------------------
following is a solution to a bit more generalized problem

Friday, October 26, 2012

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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Oct 26, 2012
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.
---
-------------------------------------------------------------------------------------------------
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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Oct 26, 2012
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.

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

yogi_Make Computed Columns For User Specified Computations Over Many Columns

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Oct 26, 2012

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:

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

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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Oct 26, 2012
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