yogi_Set Up Formulas To Get A Different View Of The Sheet As Specified By User

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #722   Aug 30, 2012     www.energyefficientbuild.com.

user sanjeev.kulshreshtha said: (http://productforums.google.com/forum/?zx=k7z311txi9j2#!searchin/docs/Compt/docs/e2HRp2Evbtg/6d0OxgxTUYoJ)
query a spreadsheet to generate different view

Hi i have a spreadsheet in following format:

Sn, Train, arr, Days, Compt Company

1,  12622, 22:30, Daily, F1, ABC Co.

2,  12622, 22:30, Daily, F2, XYZ Co.

3,  12622, 22:30, Daily, R1, ABC Co.

4,  12628, 21:15, Daily, F1, PQR Co.

5,  12628, 21:15, Daily, R1, ABC Co.

I would like to conver it in following format:

Sn, Train, arr, Days,  F1, F2,  R1

1,  12622, 22:30, Daily ABC Co, XYZ Co. ABC Co.

2,  12628, 22:30, Daily PQR Co,       , ABC Co.

Let me know how to do this?

is there any function, script or gadgets available?

I have tried query function bit it could not help.

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

following is my convoluted solution to the problem

yogi_Merge Data From Different Fields In Two Sheets Into A Master Sheet - Solution 2

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #721   Aug 29, 2012     www.energyefficientbuild.com.

user miba: (http://productforums.google.com/forum/?zx=lbtwq7u8p585#!searchin/docs/miba/docs/TZm5R4jCLTg/oDQLA25FlYsJ)

I had earlier provided a solution to a by use miba in my following blog post:

yogi_Merge Data From Different Fields In Two Sheets Into A Master Sheet

http://yogi--anand-consulting.blogspot.com/2012/08/yogimerge-data-from-different-fields-in.html 

In the following I provide a different solution to a bit more generalized problem

yogi_Set Up A Sheet For Using Vlookup Functionality For Different Formulas Not Values

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #720   Aug 29, 2012     www.energyefficientbuild.com.

user Leon Ivanov said: (http://productforums.google.com/forum/?zx=lbtwq7u8p585#!category-topic/docs/spreadsheets/91w4YrnIz5M)
Vlookup for different formulas, not values
Hello.

I have a list of priorities, names and formulas. I want to be able to vlookup for a certain formula from a list of formulas. Vlookuping for values or names is straightforward, but that's not what I want.

That is, when I change priorities, I want the formula assosiated with each priority to change as well.

There is an example: https://docs.google.com/spreadsheet/ccc?key=0AkBmRVYVGobGdHlTQ1BRWWdzcElhcVRJelMtTGhxRGc#gid=0

Thank you very much in advance.

Regards,

Leon.

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

VLOOKUP formulas lookup values and not formulas ... so in the following I have provided a workaround solution to the problem where I had to change the source formulas

yogi_Set Up An Invoice Sheet For The Week With Daily Start Stop And Break Hours And Invoice Amount Based On Hourly Rate

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #719   Aug 28, 2012     www.energyefficientbuild.com.

user BAMM Design said: (http://productforums.google.com/forum/?zx=4uvknqql63q3#!category-topic/docs/spreadsheets/cGeprFG5nWM)
Compiling hours for invoice 
I am using google docs for my timesheet. Everyday I record how many hours and minutes I take to do each task. At the end of the week I would like to add the hours of each day together and multiply them by the rate per hour to calculate the amount charged my client.
I've attached this example spreadsheet: https://docs.google.com/spreadsheet/ccc?key=0Au3preplIldTdGZGT3N4YW95NUEwMmZsY1k1VXRDaGc
I'm not savvy with spreadsheet jargon (mac user, graphic artist guy), so please explain in plain english.
Much thanks in advance to anyone who can help me figure this out.
-------------------------------------------------------------------------------
following is a solution to the problem

yogi_Merge Data From Different Fields In Two Sheets Into A Master Sheet

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #718   Aug 28, 2012     www.energyefficientbuild.com.

user miba said: (http://productforums.google.com/forum/?zx=4uvknqql63q3#!category-topic/docs/spreadsheets/TZm5R4jCLTg)
merge 2 spreadsheet into one
Hello,

Probably easy thing but I've tried to find example on the forum but without success.

I would like to marge 2 spreadsheet based on the ID column to the one. As a result I would like to have in the one row all valuesd for the same id.

See example file: https://docs.google.com/a/x-team.com/spreadsheet/ccc?key=0AurKmpGiVEEzdDJZR04wTU1hUk51aDBmQ1E2QlcxdHc#gid=2

Can you help me with a query?

Thanks in advance,

Milosz

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

it turns out that the user means merge data from different fields of two sheets into Master sheet of the same spreadsheet
following is a solution to the problem

yogi_Set Up A Table To Automatically Increment Each Successive Record For An Entity By 1

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #717   Aug 28, 2012     www.energyefficientbuild.com.

user Bruno Augusto said: (http://productforums.google.com/forum/?zx=4uvknqql63q3#!category-topic/docs/spreadsheets/kTr_DCRDRoM)
Auto-Increment 

I should describe a little better the subject, but it would be awful... >.<

In my office I have a big workbook with several different sheets and, for better structure, there are one workbook for each month.

There are a few auto-increments in these workbooks, which means the first value must be the last value, from last month incremented by one.

Once I need to duplicate the "Template Workbook" every month before start using it, I decided to add a group of cells from which I can do some sort of configurations:

+-----------------------+

|      Last Records     |

|   (from Last Month)   |

+-----------------------+

| Book ID | Last Record |

+---------+-------------+

|   A15   |    1.234    |

+---------+-------------+

|   B28   |    5.678    |

+---------+-------------+

|   C05   |    9.012    |

+-----------------------+

For future reference, let's name this range as Q1:R3.

In the previous version of this "Template Workbook", it was an easy task because each Book ID had its own sheet.

But I was wasting my working time by opening between all the sheets all the day. And then, I decided to add all records in a single sheet and when I need some specific data, I just have to query them, just like a database:

+---------+-------------+

| Book ID | Next Record |

+---------+-------------+

|   A15   |    1.235    |

+---------+-------------+

|   B28   |    5.679    |

+---------+-------------+

|   A15   |    1.236    |

+---------+-------------+

|   B28   |    5.680    |

+---------+-------------+

|   C05   |    9.013    |

+---------+-------------+

For future reference, let's name this range as A1:B5

And now the real problem about the auto-incrementing, to be added in Column B:

I need to check if the value entered in Column A is the first occurrence of that value in entire Column.

If so, the value to be added in Column B will be what I have defined in R1 PLUS 1, because it refers to the first record, of that book in that month.

If it's NOT the first occurrence, I need to find the penultimate occurence of that Book ID in Column A, grab the value of its Column B and then increment it.

Applying this idea to table above, it would be:

+---------+-------------+----------+---------+

| Book ID | Next Record |  First?  |   Use   |

+---------+-------------+----------+---------+

|   A15   |    1.235    |    YES   |  R1 + 1 |

+---------+-------------+--------------------+

|   B28   |    5.679    |    YES   |  R2 + 1 |

+---------+-------------+--------------------+

|   A15   |    1.236    |    NO    |  B1 + 1 |

+---------+-------------+--------------------+

|   B28   |    5.680    |    NO    |  B2 + 1 |

+---------+-------------+--------------------+

|   C05   |    9.013    |    YES   |  R3 + 1 |

+---------+-------------+--------------------+

I sincerely hope you can understand, because it was very difficult to me to explain.

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

following is a solution to the problem

yogi_Compute Stats in A Computed Column Based On Entries in Multiple Columns

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #716   Aug 28, 2012     www.energyefficientbuild.com.

user B-House said: (http://productforums.google.com/forum/?zx=4uvknqql63q3#!category-topic/docs/spreadsheets/jBW6-R8PRXI)
Array Formula with If forumla 
I am trying to create a formula that will determine a student's Tier (1,2,3) based on 5 criteria.

I have 5 columns(F:J) that serve as the Criteria for K

• F is the number of D grades
• G is the number of F grades
• H is the number of unexcused absences
• I is the number of tardies
• J is the number of behavior referrals

Column K is the Tier Level (1, 2, 3)

• I would like column K to fill with a 1 if:
• F:J = 0
• I would like column K to fill with a 2 if:
• G+H+J = 0
• AND I < 3
• I would like column K to fill with a 3 If:
• G+H+J > 1
• OR I >3

Does this make sense? I shared a link to my form just in case. 

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

Thanks in advance! You all do great work and I truly appreciate the help.

Carl

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

following is a solution to the problem

yogi_Compute Age In Years If Over 2 Years Otherwise In Months

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #715   Aug 27, 2012     www.energyefficientbuild.com.

user WaumbaLady said: (http://productforums.google.com/forum/?zx=hin8ylq8ptdy#!category-topic/docs/spreadsheets/NLWQI5WerE0)
age (year) calculation

I have a need that is a little special.  Would it be possible to tweak the above formula such that it would give the persons age in months IF the person was less than 2 years old?  I am director of a children's ministry at our church and this would be awesome.  I would like it to display with an "m" or "mos" after the value if possible, e.g. 18m or 

18 mos. 

Thank you for your time!!

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

in some other posts

I have provided a generalized solution providing the difference in from to dates in Years only

and also in Years, Months, and Days

In this post I provide a solution for problem posed by WaumbaLady

yogi_Enter Time In Minutes Seconds And Fractions of Seconds In Google Spreadsheet

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #714   Aug 24, 2012     www.energyefficientbuild.com.

user AMPC said: (http://productforums.google.com/forum/?zx=frqi4n9v2tm8#!category-topic/docs/spreadsheets/TUN-0q62B4Q)
TIME: minutes, seconds, hundreds
How do you input time as minutes, seconds, hundreds rather than hours .....
-------------------------------------------------------------------------------

As of now (Aug 2012) there is no direct way to enter time in minutes, seconds, and fractions of seconds in Google spreadsheet -- in the following I present a workaround solution

yogi_Create A Table Of Count Of Sales By Product And Month From Data In Another Sheet

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #713   Aug 24, 2012     www.energyefficientbuild.com.

user squash said: (http://productforums.google.com/forum/?zx=frqi4n9v2tm8#!category-topic/docs/spreadsheets/ZbFTQNCipwA)
Automatically adjusting a formula that uses a date
My spreadsheet is for tracking sales.
On Sheet 1, I'm tallying sales
Column A is the Date I'm open for business
The rest of the columns are the different items I'm selling, tallying each of the items
On Sheet 2, I'm calculating monthly totals
Column A has the various items, and the months are in Columns B-M 
I'm using this formula 
=SUM(filter('Sheet 1'!C2:C,'Sheet 1'!A2:A>=date(2012,1,1),'Sheet 1'!A2:A<=date(2012,1,31)))
to automatically calculate the number of units (tallied in Column C) I've sold of a particular item (in January 2012, in this instance).
The next item's numbers are in Column D, etc etc
My question is, is there an easy way to adjust this formula for the different months and different items? I don't know an easy way to "find and replace" pieces of a formula. Dragging the formula down doesn't make the correct change to it for the different items, and more importantly, I can't figure out any way to change the dates for the different months besides going in and doing it manually. I thought I might be able to put the portion of the formula that deals with the date
'Sheet 1'!A2:A>=date(2012,1,1),'Sheet 1'!A2:A<=date(2012,1,31)
into its own cell, and then jut reference that cell for all the formulas for a given month, but it returns errors.
Anyone have any clever ideas here? Your help is much appreciated. Thank you in advance.
------------------------------------------------------------------------------------
following is a solution to the problem

yogi_Extract From A Table Of Sales Data Sales Figures For Specified Dates Or Every Day Of A Specified Month

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #712   Aug 23, 2012     www.energyefficientbuild.com.

user SagRU said: (http://productforums.google.com/forum/?zx=xipg37otfzpe#!category-topic/docs/spreadsheets/NYrCdXlHL6o)
Advanced filtering
I'm trying to sum filtered data using advanced filtering. Advanced filtering = filtering values of one column according to values of another column (like VLOOKUP function). Here is my example: 
https://docs.google.com/spreadsheet/ccc?key=0AvHOD-oqzP4MdG83NF90VjJLQTAwTW42NS1GNThfRWc&pli=1#gid=1 
VLOOKUP doesn't work with SUM, so I have a very vague idea of how to perform such a task using more complicated functions. Thanks in advance!
------------------------------------------------------------------------------
following is a solution to the problem

yogi_Identify Names With Similar First And Last Names

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #711   Aug 22, 2012     www.energyefficientbuild.com.

user muchtolearn said: (http://productforums.google.com/forum/?zx=ahxxx0f1ye6h#!category-topic/docs/spreadsheets/HgTqZzflw08)
how to identify similar text strings 
I have a long list of names. Some of the names are the same people but they are listed slightly differently for example "John A. Smith" or "John Adam Smith". I want to do two things:

1) Use a script to detect possible same names. Perhaps using a script that detects if a certain percentage of letters are the same would work?

2) Then, I want to be presented with these possible matches and be able to decide if I want to change them to all have the same listing e.g. change all of the "John A. Smith" entries to "John Adam Smith" or to leave them the same if they are different names.   

Thanks!

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

in the following I present the little play I have had with the question by user muchtolearn

yogi_Convert Numeric Grade To Letter Grade Per Specified Rule Or Conversion Table

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #710   Aug 22, 2012     www.energyefficientbuild.com.

user archeraj2 said: (http://productforums.google.com/forum/?zx=ahxxx0f1ye6h#!category-topic/docs/spreadsheets/Om3lijdbF-M)
multiple ifs not working
i am trying to get a command working to change percentage into a grade but no matter what the percentage is is will only post an A for my grade

=IF(100.00%>=E7>=95.00% ; "A" ; IF(94.99%>=E7>=90.00% ; "A-" ; IF(89.99%>=E7>=85.00% ; "B" ; IF(84.99%>=E7>=80.00% ; "B-" ; IF(79.99%>=E7>=75.00% ; "C" ; IF(74.99%>=E7>=70.00% ; "C-" ; IF(69.99%>=E7>=65.00% ; "D" ; IF(64.99%>=E7>=60.00% ; "D-" ; IF(59.99%>=E7 ; "F")))))))))

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

following is a solution to a bit more generalized problem

yogi_Set Up A Table To Show Count Of How many Times An Item Has Been Ordered By Product And Month For Specified Year

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #709   Aug 22, 2012     www.energyefficientbuild.com.

user Hoopsnl said: (http://productforums.google.com/forum/?zx=ahxxx0f1ye6h#!category-topic/docs/spreadsheets/MYarACj1RSk)
Count how many times something has been ordered 
Hi,
Could someone help me with the following problem;
On the first tab of my sheet I have a overview what has been ordered daily. I tells me what product has been sold on a specific day.
On the second tab, the Stats tab I would like to show some statistics, I would like to see how many products were sold per month per year.
I made a test sheet to show you what I mean: https://docs.google.com/spreadsheet/ccc?key=0AgkGzPEFM52mdDVraURHUW03ZTNTYUhtMElTTWtZZFE
Hopefully someone can help me with this,
Regards,
Huub
---------------------------------------------------------------------------------
following is a solution to the problem

yogi_Compute Hours Worked By Name And Project With Working Time Hours Not To Exceed Specified Amount

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #708   Aug 22, 2012     www.energyefficientbuild.com.

user SamWolf said: (http://productforums.google.com/forum/?zx=ahxxx0f1ye6h#!category-topic/docs/spreadsheets/I1h6t62iwkU)
ARRAYFORMULA with a filter
Hello All,

I have a very simple formula in my form and i would like to make it an arrayformula.

The formula is: =sum(filter(D:D,A:A=A3,B:B=B3,C:C=C3))

I tryed all kind of combenation and i cant make it work.

----

My project is kind of simple i have 2 spreadsheets:

spreadsheet 1:

https://docs.google.com/a/saia.co.il/spreadsheet/ccc?key=0At4Q9U2JTzfJdF9SLVZLVlFHWEdrdnkyYW02ZHgwalE#gid=0 

The sheet "2012" has a list of reports coming from a form where every worker types in from what time till what time he worked on which project (coded by numbers).

The other sheet "DoneData" is the same after "fixing" the dates (taking of the "time" from the time stamp).

spreadsheet 2: 

https://docs.google.com/a/saia.co.il/spreadsheet/ccc?key=0At4Q9U2JTzfJdHRHdUtQZFN5d2ljQWcwZHM2b3Y0QXc#gid=0 

the Sheet "Sub" has a list of projects.

What i am trying to do is to have in Column "D" sum of all the Horus i worked till now. BUT every day that i worked over 10 houres needs to be calcelated as 10 Horus - not more.

I'm really sorry if it to complicated - feel free to answer no.

Any ideas?

Best Regards.

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

following is a solution to the problem

yogi_Determine The Winner From Any Number Of Games Played By Two Players

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #707   Aug 21, 2012     www.energyefficientbuild.com.

use igge said: (http://productforums.google.com/forum/?zx=ahxxx0f1ye6h#!category-topic/docs/spreadsheets/tEljLsZs0gA)
Comparing amount of cells in order to determine a result
Hello. I've tried many different approaches to this problem, but I can't seem to find a solution.
Player A and B play 3 sets in a game. The scores of those sets are represented in columns B3 through D4 like this: 
     A      B C D
1           Game1
2 Set:      1 2 3
3 Player A: 5 2 1
4 Player B: 2 3 1
5 Winner:    tie 
On row 5, in a merged B-D cell, I want to calculate a winner based upon how many sets they won. If someone won 2 sets they win the game, and also if they won only 1 but the 2 other sets were tied. Likewise, in case of a tie (1 win each and 1 tied game or 3 tied games) the result should be "tie".
Like I said I've tried to do this in several ways using COUNTIF or ArrayFormula, but I just can't seem to get it to work. Using the line below I'm able to display the winner of each set, but I still need to combine this data to determine an over-all winner:
=ArrayFormula(IF((B3:D3)>(B4:D4), "Player A", IF(ArrayFormula((B3:D3)<(B4:D4)), "Player B", "tie")))
Any ideas?
Thanks!
-----------------------------------------------------------------------------------------
following is a solution to the problem 
in Sheet2, I have provided the solution to a bit generalized problem in finding the winner from a specified number of games played regardless of the total number of games played.