Friday, January 27, 2017

yogi_Look At Entry In Each Row Of Column D If The Entry Has Not Occured In Rows Above Mark 1 Otherwise Leave Blank

Google Spreadsheet   Post  #2114
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-27-2017
question by Brad O.:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/QCPcSjXiJVQ;context-place=forum/docs
Array Formula for a variable range
I've been trying hard to get an array formula to work for my specific issue and I need some help with it.

I need an array formula that will auto expand into new rows.  The formula needs to search all previous rows only for a value from the new row.  If it's present, insert a 1 into the cell, and if not, do nothing.  Basically, I'm just checking if this is the first time the value appears in the sheet and if so, mark it with a 1.  Future occurrences of the same value would be ignored.

Here's an example of the formula that works, if I manually copy it into each row: 

=IF(countif($D$2:D9,D9)=1,1,"")

I've tried every way I could think of to convert this to a working Array formula that would auto expand into all future rows but I cannot seem to find the right way to do it.

Any suggestions on how to make this happen, either with Array Formula or other ideas would be greatly appreciated!

Tuesday, January 24, 2017

yogi_Compute Number Of Entries During Each Shift Shift Begin And Shift End Times Are Not On Same Day-02

Google Spreadsheet   Post  #2113
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-24-2017
question by Holiday Claims:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/wSuFOUUGqTg;context-place=forum/docs
Number of entries added between two different time-stamps, Not on same day
We start our shift at 11pm in the evening and finish 7am the next morning. What formula can I use to see how many entries are made during each shift?

18/01/2017 11:55:40
Day 1
Shift Starts 11pm 18/01/2017 Finishes 7am 19/01/2017
19/01/2017 06:55:40
19/01/2017 23:45:41
Day 2
Shift Starts 11pm 19/01/2017 Finishes 7am 20/01/2018
20/01/2017 00:00:07
20/01/2017 00:03:18
Day 3
Shift Starts 11pm 20/01/2017 Finishes 7am 21/01/2019
21/01/2017 00:03:18

Sunday, January 22, 2017

yogi_Compute Number Of Entries During Each Shift Shift Begin And Shift End Times Are Not On Same Day-01

Google Spreadsheet   Post  #2112
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-22-2017
question by Holiday Claims:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/wSuFOUUGqTg;context-place=forum/docs
Number of entries added between two different time-stamps, Not on same day
We start our shift at 11pm in the evening and finish 7am the next morning. What formula can I use to see how many entries are made during each shift?

18/01/2017 11:55:40
Day 1
Shift Starts 11pm 18/01/2017 Finishes 7am 19/01/2017
19/01/2017 06:55:40
19/01/2017 23:45:41
Day 2
Shift Starts 11pm 19/01/2017 Finishes 7am 20/01/2018
20/01/2017 00:00:07
20/01/2017 00:03:18
Day 3
Shift Starts 11pm 20/01/2017 Finishes 7am 21/01/2019
21/01/2017 00:03:18

Saturday, January 21, 2017

yogi_From 'Form Responses Sheet' Compute Monthly Totals By Items Built

Google Spreadsheet   Post  #2111
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-21-2017
question by jrh456:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/aJXAAwNHyVI;context-place=mydiscussions

Need SUMIF to work for a multisheet equation

 Please assist me as I have tried to follow along but these equations make no sense and I am very lost I have a form that will be filled out by employees and the responses feed into a response sheet, I then need monthly totals from that response sheet I know I am supposed to use a SUMIF formula but it does not seem to work at all. In other words if the timestamp is January then it should total in my january total;s 

Sunday, January 15, 2017

yogi_Group Total From Table Of Entities And their Corresponding Amounts

Google Spreadsheet   Post  #2110
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-14-2017
question by tiledude:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/qNW8dJag7PM;context-place=forum/docs
Arrays and adding columns of data

Hello,

This formula works fine and is giving the correct prices based on AW. 

I am trying to add the data from AW.

basically trying to add another column of data.

=QUERY('Copy of 01-FBA'!4:1000, "select SUM(R) GROUP BY AW label SUM(R) 'R'")

thanks for any help :)


RAW
14.99data from AW
439.82data from AW
1169.52data from AW
299.86data from AW
194.91data from AW
14.99data from AW
169.9data from AW
209.9data from AW
49.98data from AW
39.98data from AW
304.83data from AW
174.93data from AW
144.93data from AW
99.96data from AW

Saturday, January 14, 2017

yogi_Reverse Entries In Sheet Named Data Horizontally (except for entries in first column)

Google Spreadsheet   Post  #2109
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-14-2017
question by PY_:
https://productforums.google.com/forum/#!topic/docs/CZjRNAgMyUg;context-place=mydiscussions
Reverse data horizontally
I have a massive spreadsheet of data that can grow horizontally and vertically. What I'm trying to figure out how to do is duplicare that data on another sheet only in reverse order horizontally using formulas or a script starting at column B. Is there a formula or script I can use to accomplish this? Keep in mind that the column and row numbers change so the formula/script needs to be able to change with it.

yogi_What Words Occur Most Often In Range A:A And Their Corresponding Frequency

Google Spreadsheet   Post  #2108
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-14-2017
question by hlopez_:
https://productforums.google.com/forum/#!topic/docs/T2fqkh1OHHU;context-place=forum/docs
Count every word in column and display word and frequency
I have a column of 4000 entries. Each entry has a sentence. I would like to know what words occur most often in the A1:A range and their corresponding frequency

Wednesday, January 11, 2017

yogi_Add Specified Text To Range Of Cells Containing Phone Numbers

Google Spreadsheet   Post  #2107
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-11-2017
question by DrNasef OrthClinic:
https://productforums.google.com/forum/#!topic/docs/DuLlELkK_PI;context-place=mydiscussions
How to add specific text to range of cells containing cellular phone numbers?
Hello I was trying to edit my exported google contacts as a google spread sheet and edit the telephone numbers, unify them, and delete the duplicates. 
I noticed almost all my numbers lack the starting digit "0" for cellular numbers. (Proper format being : 0XXXXXXXXXX)
I would like to add the following : "+20" to all these numbers in a certain column

So say I would like to add "+20" to all cells in column AL from AL1 till AL3480 
Is there a "formula " for that ?
Thank you and naturally any help is highly appreciated

P.S
If anyone could also point out how to erase the duplicates in the easiest possible way I would be very grateful. It is a real pain sifting through almost 4000 contacts!!

Sunday, January 8, 2017

yogi_Sort Inventory In Column A By Article Size In Ascending Order (not all articles have size embedded at the end of article name)

Google Spreadsheet   Post  #2106
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-08-2017
question by Tammy Ellis:
https://productforums.google.com/forum/#!topic/docs/jnlSJLvgts8;context-place=forum/docs
Looking for help with adding a filters by last words in a row
I have an inventory spreadsheet and would like to be able to filter based on size of inventory.  I attached a copy of the Drive spreadsheet and would like to filter the first column by e.g. 2-4, 6-8, Petite, Plus.  Each size has a print name in front (e.g Blue Canyon 2-4, Blue Canyon 6-8)...I'm a novice....any help would be appreciated. 
Jan 6 Inventory copy.ods
14 KB
Jan 6 Inventory copy.ods

Friday, January 6, 2017

yogi_Get Unique List Of Entities And Their Respective Count From A Single Column List Of Entities

Google Spreadsheet   Post  #2105
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-06-2017
question by: jschram:
https://productforums.google.com/forum/#!topic/docs/9HfIjoM9cU8;context-place=forum/docs
QUERY to Aggregate and Group By a single column
I'm surprised by the behaviour of the QUERY function if I have the below sample data...

Names
--------
Andy
Susan
James
Janice
Susan
Larry
Janice
Susan
Andy

With the formula: 

QUERY(A1:A10,"select A, count(A) group by A", 1) 

And I would expect to produce a result similar to a pivot table with the columns Distinct(A) and Count(A) however the formula fails to parse. I have read the QUERY documentation and I didn't find anything that specifically forbids multiple uses of the same Column Id in a query. I was playing with some ideas on this sheet but I'm open to any advice,

yogi_From A Composite List Of Entities Count Entities Considering Case Sensitivity

Google Spreadsheet   Post  #2104
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-06-2017
question by: ASchwab:
https://productforums.google.com/forum/?visit_id=1-636193431124668080-3096751358&rd=1#!topic/docs/PWBgW9TKCp0;context-place=forum/docs
Counting capitals versus lowercase letters?
I have cells with the following items in it:

(c)
(b, B)
(c, b)
(cc, bb, b)

etc.

Basically, all combos of c's, b's, and k's in parenthesis. Some are capital letters, some are double letters.

Each set is in it's own cell.

Now, I am trying to get a count of these.  Basically, something like:

b = ##
c = ##
bb = ##
B = ##
C = ##

I can get it for the double letters, and single letters.  But, now I cannot get it to differentiate between capital letters and lowercase letters on the countif function.

Is there a way to get it to ONLY count if there is a capital letter and ONLY count if there is a lowercase letter

Thursday, January 5, 2017

yogi_In Table Of Lesson Plan Compute Running Balance Of Amount Due By Student In DropDown List

Google Spreadsheet   Post  #2103
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-05-2017
question by: Lance Jacobs (NYC):
https://productforums.google.com/forum/?utm_medium=email&utm_source=footer#!msg/docs/Z_CBHPYQUww/NDPR2NEsDAAJ
Arrayformula to track Current Balance in Transaction Register
My sheet: 

Hi everybody,
My sheet will track a piano teachers lessons and payments. My challenge is to show Current Balance as students receive lessons and make payments.

I thought it would resemble a check register, but it got complicated by the fact that she has more than one student.

I've got a working formula in Column G – It calculates the current balance by looking at previous entries, and subtracting all payments from all fees as filtered by student the student entered on that row. But it's not an arrayformula, and I know she'll want to insert rows.
Anybody got any ideas?
Lance

Monday, January 2, 2017

yogi_From Table Of Dates And Visitors Create A Table Of 'Week Number' 'Unique Visitors' And 'Number Of Visits'

Google Spreadsheet   Post  #2102
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-02-2017
How to list by week and count unique cells in adjacent column
I have a table with two columns. In the first column I list the dates ("yyyy-mm-dd hh:mm" format) and in the second column I list the names (visitors).

Column 1 Column 2
2016-12-20 16:04 John
2016-12-20 20:02 Mike
2016-12-21 10:36 Mike
2016-12-22 11:15 George
2016-12-24 12:11 George


I would like the output of the formula to be on three columns: Col1-unique number of weeks, Col2-count unique number of visitors during the week, Col3-count number of visits. The diference between Col2 and Col3 is that in the first one a visitor will only be counted once no matter how many visits he had during the week while in Col3 each visit will be counted.

Since 08/15-21 is week 33 and 08/22-28 is week 34, the output should like this:

Week Unique visitors Number of visits
33 2 3
34 1 2

Based on what I've read so far, I manage to use the following function to get unique number of weeks (group by week) and number of visits:

query(index(if({1\0};WEEKNUM(DATA_INFO_3!C2:C);DATA_INFO_3!D2:D));"select Col1,count(Col2) where Col2<>'' group by Col1 label count(Col2) '' ") My problem is that I do not manage to count the unique number of visitors as grouping by both Col1 and Col2 will return an error. Your help would be much appreciated.