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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-27-2017
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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-24-2017
question by Holiday Claims:
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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-22-2017
question by Holiday Claims:
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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-21-2017
question by jrh456:

# 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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-14-2017
question by tiledude:
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

## Saturday, January 14, 2017

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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-14-2017
question by PY_:
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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-14-2017
question by hlopez_:
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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-11-2017
question by DrNasef OrthClinic:
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)

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-08-2017
question by Tammy Ellis:
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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-06-2017
question by: jschram:
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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-06-2017
question by: ASchwab:
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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-05-2017
question by: Lance Jacobs (NYC):
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'

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.