yogi_Split Cell Contents In Time Tracking Data Dump

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #742   Sep 12, 2012     www.energyefficientbuild.com.

user outlaw26r said: (http://productforums.google.com/forum/?zx=4yq9wcrgo0dm#!category-topic/docs/spreadsheets/hmFjPVy_M24)
Splitting Data cell results in 0 being removed from front of number (0178)
I am taking calendar data from my team that I have extracted from Google Calendar & I plan on weekly importing into my spread sheet titled Time Tracking Data Dump. I am then splitting the text in A:A to isolate the project number which will come before the ":" into columns F:G. This will enable me to pull all the relevant calender items that match the project number to another spreadsheet. My issue is that when I split the cell as seen in Column F3:G3, the project number is there, but it is missing the 0 in front of the 0178. This is crucial as I am already calling from several other spreadsheets by the project number so I can't just loss the 0. 

I have attempted to change columns A:A & F:F to plain text in hopes of retaining the true number but so far have not been successful. Any ideas would be greatly appreciated.

Thank you,

Outlaw26r

Justin

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

yogi_Sort Sheet By Columns Based On Values In A Row

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #741   Sep 11, 2012     www.energyefficientbuild.com.

user Estauss said: (http://productforums.google.com/forum/?zx=4yq9wcrgo0dm#!category-topic/docs/spreadsheets/_1CJJFTzwEo)
How to sort sheet based on values in a row

I am running an office pool for Football picks. Last Year I used Excel, this year I decided to use Google Docs so that I can share the sheet over the internet.

Here is the link to the document:

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

Each person picks the winner for the entire season. So I have 17 sheets, one for each week where I input their picks for that week. The final sheet has the results for each week and the season as a whole.

What I want to do is sort the sheet based on the value in Row 20. Who ever has the most picks correct their column would appear first, and so on down the line. In excel I would simply select the columns and choose sort by ROW sort on VALUES order LARGEST to SMALLEST. I can't seem to find a way to do this with Google.

Any help would be greatly appreciated.

I am using Win7 as my OS and Chrome as my browser.

Thanks

Eric

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

following is a solution to the problem

yogi_Extract Sum Of Top1 Through Top5 Amount Respectively Of Each Group

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #740   Sep 08, 2012     www.energyefficientbuild.com.

use meph2u said: (http://productforums.google.com/forum/#!mydiscussions/docs/jLvJYM7FkfI)
Calculating a sum of the top values in the groups. 
https://docs.google.com/spreadsheet/ccc?key=0Atrb4wVf_dsSdGlMcTlIVnhZUjJ3NHYxcnpTTWlCMGc#gid=0 
I have sorted the data by amount within group for illustration purposes, but the data would normally be available in transaction order (or random order).
1) is there a way to calculate the values in J2 (through N2) using only the data in columns B and D regardless of how the data is sorted (if at all)?
2) is there a way to calculate the values in E regardless of how the data is sorted (if at all)?
----
So if there is a way to calculate that on based on B&D, then the rest works great.
If it can all be done in one pass, even better.
--------------------------------------------------------------------------------------
following is a solution to the problem

in response to additional questions from use meph2u, I have provided the sum of Top1 through Top 5 Amount Respectively of each group and also formula for computing a sequecnce of 1 through 5 for each group in Sheet2

and a more generalized formula and an illustration for sequencing of Top 1 through Top how many? and also the sum of Top1 through how many? in Sheet3

yogi_Pull Data From Sheet1 Ordered By Pending First Then Date Received In Ascending Order

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #739   Sep 08, 2012     www.energyefficientbuild.com.

user sparkplugmag said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/qLT0EvxJd08)
How to filter dates , like an alarm clock for my job board??
Hi All,

I have a job board spreadsheet for a printing company i work for. I'm trying to create a filter formula that will filter the dates to list the older jobs that have not been finished, by oldest dates to newest (I have a column that will be entered with "finished" or "Pending"). I am so new to formulas, was seeing if i could get a little advice on how to maybe accomplish this.

big thanks ahead of time

b

----

here is the link to the spreadsheet:

https://docs.google.com/spreadsheet/ccc?key=0AoeBTy3G6zODdEVFcURmc3MyaV96QjZVeVBVY3Q0c0E&pli=1#gid=0

I would like to filter the data by date received and the status of "pending" so i can see the important jobs that need to be completed by my company first over later received jobs. The "DATE COMPLETED" column would have either pending or a completed date. If it has "PENDING" i would like it to filter those jobs by the Date received (oldest to newest). Any help to accomplish this would be greatly appreciated

thanks

Billy

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

following is a solution to the problem

yogi_Compute Scores Row By Row Within The Form Responses Sheet

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #738   Sep 07, 2012     www.energyefficientbuild.com.

user chlordk said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/how-do-i/desktop/d3CL12H3HpM)
Result from form post can not be calculated automatically, multiple choice questionnaire
I have a multiple choice questionnaire like match these 5 capitals to these 5 countries. After the posting I want the user to see his own result and also all others result.

I have made with one sheet for the posting. This comes by default when using a form. Then I have added a new sheet where I have pre-made the result for a number of answers. So if one add an answer #1 it will be in row #2. On the result page I then match the answer with heading of the column, like this =Quiz!B3=B$1 - so if the answer in Quiz!B3 is the same as in Result!B$1, then I get a TRUE. After that I can count all the TRUE's and then get the score.

The problem is that the result page loose the reference to say cell Quiz!A2. The cell Result!A2 is supposed to point Quiz!A2 but after I post when the cell Result!A2 is pointing at Quiz!A3.

The first result is located in Result!W2. Result!W3 is my test to see if everything is correct.

Here is the form:

https://docs.google.com/spreadsheet/viewform?formkey=dFduWE04WE1CVHdWdDhLUmQ5c3dRU1E6MQ#gid=1

Answers and result page:

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

Actually I would like to one big block with all 20 countries but Google can only handle 5 in a block. Please don't tell I made it all wrong as it took quite some time to make it this way.

Feel free to try the test. I warn you - the average on correct answers are about 6 of 20.

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

in the following I have presented a solution wherein I have computed the scores row by row within the so called Form responses sheet

[and if you want to use a separate Result sheet as you currently have in your set up, you can use the following array formula in cell W2: =ArrayFormula(mmult(B2:U*1,transpose(column(B2:U)^0)))]

yogi_Tabulate Data From Stats In A Table Into Another Sheet By CSR And DispositionType In Pivot Table Style

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #737   Sep 06, 2012     www.energyefficientbuild.com.

I had presented a solution to a question by user locomambo (http://productforums.google.com/forum/?zx=1gy6fxv2nn5x#!category-topic/docs/spreadsheets/RfrTcPQ9SmA) in the following post:
http://yogi--anand-consulting.blogspot.com/2012/09/yogitabulate-data-in-soldby-and.html
This is an extended solution to the actual data in the user's spreadsheet

yogi_Tabulate Data In SoldBy And Disposition Columns In Another Sheet By RepsID And DispositionType

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #736   Sep 06, 2012     www.energyefficientbuild.com.

use locomambo said: (https://productforums.google.com/forum/#!msg/docs/RfrTcPQ9SmA/PAEDdWr4e2QJ)
i have a problem and cant figure it out..i have a google doc with 2 sheets, in the 1st sheet called details i have a column called "sold by" with the csr ids in them and another column called "disposition" with the words "active" or "cancelled" in them..and in the 2nd sheet called "totals" i have a column with each rep id with another column with active and another with cancelled..

i just need to count how many active/cancelled sales does each rep has to show on the 2nd sheet

ex:.

sheet1 sold by      dispositions           

              110         cancelled                                             

              100         active                                          

              110         active

              100         cancelled

              102         active

  2nd sheet      reps id        active     cancelled

                         100             1           1

                         102             1           0

                         110             1           1

of course this is where the formula comes in place...i have try countif, counta ect..please help.i would really appreciated thanks

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

following is a solution to a bit more generalized problem