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!

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

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

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 

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 :)

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

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

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!!

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

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

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: 

https://docs.google.com/spreadsheets/d/1ku2u5IhD6KQ63g8wNbwx7WO7FFWCzP-BUpGNS_mO37E/edit?usp=sharing

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

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

question by: DanielAsk:

https://productforums.google.com/forum/#!topic/docs/buqGkCOUOcA

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.