## Friday, February 28, 2014

### yogi_Keep A Cumulative Count Of Hours By Name 1 Hour For Each Instance Of Name In A Calendar - 2 - horizontal layout

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-23-2014
post by Mike Bailey 1 (https://productforums.google.com/forum/#!msg/docs/i3md8VPZheo/ujqg9OUhKPkJ)
Count and Tally

this is an extension to the solution to the problem earlier presented in my following blog post:

yogi_Keep A Cumulative Count Of Hours By Name 1 Hour For Each Instance Of Name In A Calendar
-----------------------------------------------------------------------------------------------------------------------------

here I have presented a solution by changing the calendar to a horizontal layout -- that yield to a solution using simpler and fewer formulas

### yogi_Keep A Cumulative Count Of Hours By Name 1 Hour For Each Instance Of Name In A Calendar

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-23-2014
post by Mike Bailey 1 (https://productforums.google.com/forum/#!msg/docs/i3md8VPZheo/ujqg9OUhKPkJ)
Count and Tally
Hello,

I am extremely untalented with spreadsheets and Excel, so excuse me if this is day 1 stuff !

I am populating (manually) cells in a "calender" with one of a possible n names (text)
When fully populated, the Calender might have 2 or 3 different names spread over 12 hours, on each day.
I can count the total number of "hours" for each name easy enough (it seems) but what I would also like
to do is this.

A
1 John
2 John
3 John
6 Phil
7 Phil
8 Phil

in column B, next to each name, I would like to have a cumulative counter of hours at that point.
So B1 would be 1 for John, B2, 2 for John, but B4 would be 1 for Adam, B5 2 for Adam etc
Thing is, I need to check who is in A first as I have a fixed list of names.

Any help would be great, I have not actually made a sheet yet..I am stuck on step 1 !
---

so..I can count the total hours, for any name in the whole month day.
Now I would like to start adding up the hours for each name, in the column next to it.

Does that make sense?
-----------------------------------------------------------------------------------------------------------------------------

have a look at my convoluted solution -- I have shown formulas for 1st week only -- these can be extended to subsequent weeks

## Saturday, February 22, 2014

### yogi_Create An In Out Time Sheet For WorkDays By Employee From A Database of Date_Time_Employee_In/Out

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-23-2014
Query if certain conditions are met
Hi, I am having trouble with this. I have included a sheet which has the formulas, and how I think they should work, but they don't. Thanks for your help.

I have created a database that is continually updated, this is a simplified example:
 Sun 2/16/14 08:00:00 AM Campbell Jason In Sun 2/16/14 11:30:00 AM Campbell Jason Out Sun 2/16/14 12:30:00 PM Campbell Jason In Sun 2/16/14 04:30:00 AM Campbell Jason Out Mon 2/17/14 11:00:00 PM Campbell Jason In Tue 2/18/14 01:30:00 AM Campbell Jason Out Tue 2/18/14 02:00:00 AM Campbell Jason In Tue 2/18/14 08:00:00 AM Campbell Jason Out

I have also created a time sheet that looks similar to this:
 Employee Campbell Jason Date Sun 2/16/14 Mon 2/17/14 Tue 2/18/14 In 08:00:00 AM 11:00:00 PM Out 11:30:00 AM 01:30:00 AM In 12:30:00 PM 02:00:00 AM Out 04:30:00 AM 08:00:00 AM In Out

All I need is for the time to be populated into the correct location. Here is some detail below.
1. The database and the time sheet will be in the same file, but on different tabs. In the shared file I have copied them to the same tab while trying to figure it out.
2. To use this spreadsheet an employee is selected from a validated list
3. I want the time sheet to populate with the correct time based off of 3 different criteria and 1 equation
• The employee name
• The date
• In or out
• Is the time being populated after the prior recorded time with the same In/Out value
4. We have night shifts that go into the next day, I would like them to be placed together if possible(see red highlighted time) if not then my time value calculations need to be tweaked(they currently work but are not included in the shared file)
---------------------------------------------------------------------------------------------------------------------------------------------

### yogi_Conditionally Sum From Column Of Another Sheet Entries That Meet Several Specified Criteria

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-22-2014
=ARRAYFORMULA(if(LENB6:B); (SUM(FILTER(Sheet2!F:F; Sheet2!A:A=b6; sheet2!...)))" "))
What am I doing incorrect???
I would like to auto populate Col A with the formula in the TITLE
=ARRAYFORMULA(if(LENB6:B); (SUM(FILTER(Sheet2!F:F; Sheet2!A:A=b6; sheet2!...)))" "))
THis formula grab the sum value in row 6 and populates that value down.

ANy HELP!!!
---
Hi Yogi,

My title is not in B6:

In Col. A I am trying to auto fill a formula which get a summed total based on certain criterias.
If(LEN(B6:B); sum(Filter(QueryAllOrders!F:F;QueryAllOrders!A:A=B6;QueryAllOrders!E:E=F6; QueryAllOrders!D:D=E6)),"---")
The above formula works, but when I put ArrayFormula(If(LEN(B6:B); ......... all I get is the total of the first row and that value is filled down.

Sheet18 -- is where I am trying to perform this action.

Thanks,
Ash
----------------------------------------------------------------------------------------------------------------------------------------------------------

=If(LEN(B6:B); sum(Filter(QueryAllOrders!F:F;QueryAllOrders!A:A=B6;QueryAllOrders!E:E=F6; QueryAllOrders!D:D=E6)),"---")

does not lend itself to be used as a multicell array formula for cells B6;B

so one way is to use the convoluted formula I have presented for cells B6:B in this post

### yogi_Conditionally Format Non-Blank Entries That Are Not The First Instance

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-22-2014
Highlighting Duplicate Rows
I have a spreadsheet with the following columns :
A - first name
B - last name
C - A&B   (full name)

As I key in entries to this spreedsheet, I'd like to have a 4th column that will auto complete upon my entering a name, as to wether this name is a first, or a duplicate further up the spreadsheet.  Is this possible? I've tried to read the COUNTIF or IF functions, and cannot figure it out.

Thank you for any help!
------------------------------------------------------------------------------------------------------------------------------------------------------------

### yogi_Arrange A List Containing Text Numeric Or Blanks In Reverse Order

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-22-2014
list column backwards from dynamic list
Hi, I can't seem to list a column in reverse order.  I could do it if the original column did'n change size.  If  I had "cat, mouse, dog" in a column I want the new column to read "dog, mouse, cat"  The size of the column will be changing so I can't put in set values to manipulate data
---------------------------------------------------------------------------------------------------------------------------------------------------

## Friday, February 21, 2014

### yogi_Extract Single Character Left Of / Left Of x.html At The End Of The URL String

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-21-2014
Hi All,

Not really too sure how to do this how do I have a series of URLs in Column A and split that string up to include only the second last part of the string (if it exists). The URL length may be different.

For examples I have a spreadsheet. In Column A there are a series of URLs e.g

https://www.example.com/a/b/c/d/e/f.html  Value I want captured = e
https://www.example.com/a/b.html Value I want captured = a
https://www.example.com/a/b/c/d.html Value I want captured = c
https://www.example.com/ Value I want captured = " "

I have tried doing various regex but it's way above me. Have also tried split function but that splits everything

Basically what I want is a spreadsheet where I can input a list of URLs in Column A and then pull a child and parent hierarchy in Column B & Column C (the page f.html being a child or e, the page b.html being a child of a, etc ) to use an organisational chart to graph the hierarchy.

Is this possible?
-----------------------------------------------------------------------------------------------------------------------------------------------------------

in the following solution I have assumed the url string ending with /x.html

### yogi_Extract The Last Non-Zero Number From H3 to H That May Contain Numbers As Well As Text Entries

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-21-2014
Getting the last item in a list
I'd like to have a cell get the last item in a list. They're all dollar values and if it's \$0.00 it seems it sets the cell to \$0.

Example:

H3=\$1000.00
H4=\$1200.00
H5=\$1100.00
H6=\$0.00

then F1=\$1100.00

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

### yogi_Conditional Formatting For Multiple Ranges For A Combination Of AND OR Conditions Using Google New Sheets

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-21-2014
Conditional Formatting - Shade one cell based on another
I have created a generic version ofthe spreadsheet. Here is a link. I have been able to create the proper formula (=IF(AND(L6="",TODAY()>G6),"Late","OK"), see column P. I can then create the conditional formatting formula based on the result of that formula, see column D. However, I can not have the formula in column P be the custom formula in the conditional formatting. Is there a way to do this and eliminate an extra step?
-----------------------------------------------------------------------------------------------------------------------------------------------

## Thursday, February 20, 2014

### yogi_Compute Number Of Sales By SalesPerson From Whole And Split Sales And Create A LeaderBoard

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-20-2014
Tally cells across row based on frequency in another column
Hi,

I have a fairly specific question, and I don't even know if it is possible to do what I am asking.

The sheet I am using is on the new spreadsheet platform.

I work in a retail industry, and I am trying to populate a sales leaderboard based on recorded sales in another sheet. I sequentially want each instance on a sales log to populate a square with a color (or a number and I can conditionally format the cell to change colors) on another sheet. To complicate things, I want the sheet to be able to differentiate between a full sale or a split sale.

Here is a dummy sheet that represents what I am asking:

Basically, for each instance that sale is recorded for a salesperson, I want the leaderboard to automatically populate itself.

Thanks in advance, I know this is quite complicated.

T.J.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

### yogi_Pull Row By Row For Each Job Name Associated Job ID From A Table In Another Sheet

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-20-2014
Find values based on a filtered list of values
I have a sheet "Time Sheet" where an employee will select a Job Name from a drop down list that is populated from another sheet "Current Jobs". Column A is a list of Job Name and column B lists Job ID in "Current Jobs". I have a column Job ID in "Time Sheets" that I want to be populated from "Current Jobs" based on the selection of the Job Name. I am using validation for the Job Name which works fine but can't figure out a way to populate the Job ID based on the selection. I am using the formula =ArrayFormula(if(G11:G="","",vlookup(G11:G,CurrentJobs!A:B,1*row(G11:G)^0,0))) in H11 - G11 is the dropdown where the Job Name is selected. With this formula H11 is being populated with the Job Name instead of Job ID. Not sure what I am doing wrong...
Thanks in advance for any help.
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

formula in sheet PP1-14

## Wednesday, February 19, 2014

### yogi_Find Names In Column A Associated With The Specified Number of Largest values In A Specified Column

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-19-2014
Find values based on a filtered list of values
Good evening,

In A1:F6 I have a small "database".

The first row has a list of items which currently is finite, but I may need to add more in future. However these possible additions will not too much at once, though.

The first column holds a list of people names and following columns how much of each listed items they have.

In "Column H" I started testing how to filter the whole "database" to the ones matching the expression "Item #3".

But I'm having problems with next two steps, assuming they're possible:

1. Find the two largest values in this filtered list.

2. With these two entries, get the Name associated to them.

I hope the subject did not confuse you and I thank you in advance.
---