## Sunday, October 14, 2018

### yogi_Compute Years Months Days Since Member Join Date 1

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Oct-14-2018
count from date - teammember since date
Hello.

I use Google Sheets for my team.
There is a list of all teammembers.
I want to make that:

User1   |    joined on 10-09-2018   |   teammember since X years, X months, X days

How can I make the "teammember since"-feature?
With =DATEIF it doesnt work.

Thank you.

## Sunday, October 7, 2018

### yogi_Compute Years Months Days Since Member Join Date

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Oct-07-2018
count from date - teammember since date
Hello.

I use Google Sheets for my team.
There is a list of all teammembers.
I want to make that:

User1   |    joined on 10-09-2018   |   teammember since X years, X months, X days

How can I make the "teammember since"-feature?
With =DATEIF it doesnt work.

Thank you.

## Thursday, October 4, 2018

### yogi_Return a value from a cell in 1 column only if 2 other cells are consecutively 0 then 1

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Oct-04-2018
Return a value from a cell in 1 column only if 2 other cells are consecutively 0 then 1
I have a column (let's call it column E) where each cell in the column is a different time (i.e. 10:44:00 AM, 10:46:00 AM, etc...). I have another column (let's call it column F) where each cell in the column is either a 1 or a 0. What I want to do is get a return from column E only if 2 consecutive cells in column F are specifically a 0 and then a 1. For example:

F3 & F4 would give me E4
F15 & F16 would give me E16
Those would be the only values that I would get back from the columns provided in the above screenshot. Thanks in advance!

## Saturday, September 29, 2018

### yogi_Compute Moving Average Of Scores For Successive Rounds

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Sep-29-2018
How to do a running/moving/rolling average?
Hello,

It seems like a very simple function or formula to need and use, however, I can't seem to find anywhere which explains how to do this in layman's terms.

So here's an example of some data (imagine each comma is a separate cell):

Round - 1, 2, 3, 4, 5
Score - 7, 11, 6, 2, 2

Of course, I can easily work this small sample out manually, but I need this to us on a much larger scale.

Worked out manually, the moving average for the score per round would be the following:

7, 9, 8, 6.5, 5.6

What function/formula can I use for Google Sheets to work this out automatically for me?

Your help would be much appreciated.

Thank you.

Kind regards,

Jake

### yogi_From A:B Pull In D:E Data Only For Names Specified In Column C

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Sep-28-2018
Two columns. Copy every single match (inc. doubles) from two columns to a third column?

This is simply a trial version. In reality, I am attempting to keep track of student records of attendance by class.  In my real document, I have 2500+ students, with 400+ possible classes, just to give you an idea of scale.  The fake document I have above gives a decent idea.

I have a list of students in column A and the point they earned for something in column B.   I need to take the students that are listed in Column D and pull every single match from column A, meaning if John is listed 3 times, I need all 3 of his "points earned" and not just the first value, and put all of those names and the corresponding values into F and G.  They do not need to be sorted, as the initial 2500+ name list I run is already sorted when I begin.

Any help is super appreciated.

### yogi_ Pull Information from another Tab And Compute Win Loss Scores For Listed Teams

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Sep-28-2018

# Error: An array value could not be found

Hello, everyone!

In Country List, Column B, I have names of countries. In another sheet, I want to check if the value of each row in column B is "Bulgaria", in this example.

When I do this:
=IF('Country List'!B59:B66 = "Bulgaria", "works", "doesn't work")

from Country List, it works*, but when I do it from any other sheet, I get "An array value could not be found". I'm using the exact same formula.

*When I say works, i mean doesn't return an error.

"Bulgaria" is the value of the cell B64, but it returns "doesn't work". Starting to think I'm not using the formula correctly.

I'd appreciate any kind of advice.

## Thursday, September 27, 2018

### yogi_For Entities In Column A Find The Latest Associated Date In Column B

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Sep-27-2018

# Stuck on a data analysis problem

I'm somewhere in the middle between a noob and a power user when it comes to google spreadsheet function.  I have experience with array formulas, but have met my waterloo when it comes to this problem.

I have some data that contains a "thing" (in this case a legislative bill) followed by several rows of elements related to that bill.  Among those elements are the dates of most recent actions related to the bill.

I need a formula that finds and evaluates those dates to find the most recent one.

I've tried several angles of attack on this simple problem, and am defeated.

I'd be grateful for any advice or help.

A more detailed explanation would be difficult. Real sample data and more description of the problem are here:

## Tuesday, September 25, 2018

### yogi_Count Number Of Projects Completed By Month

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Sep-25-2018
Looking to calculate how many cells are found in each month.
Looking to calculate how many cells are found for each month.   I used a SUMPRODUCT formula in excel and have switched to google sheets and the formula will not work.  (different sheets but edited to code to reflect properly)

=SUMPRODUCT(--(TEXT('Live Project Queue'!K:K,"m")="8"))

Ideally, I would have a new tab for Results and can sort the completed projects as they are completed based on the month the dates are associated with. so I can then view completed projects for August, Sept, Oct, etc... then repeat for 2019 which would just be repeating the formula etc.

Any help is appreciated. Red outline is the column where I am getting the completed dates.
Screen Shot 2018-09-24 at 2.37.35 PM.png
146 KB

## Monday, September 24, 2018

### yogi_Pull Only Specified Columns From File flightlogs.csv

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Sep-24-2018
CSV into Spreadsheet , certain columns filter
Hi to all!

Can somebody help me. I am populating google sheet from external csv file from another website. Its wotking ok, i have implement script for auto update. But i want only certain columns from that csv file to be shown on google sheet, how can i do this. I have for expample 20 columns, but i want only 5 of them...Thank you

## Thursday, September 20, 2018

### yogi_Pull Parts of Strings From Cells B2:B Into Cells E2:E

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Sep-20-2018
REGEX wizz? ArrayFormula to strip unnecessary text at the start and end of a cell's value?
I need hep with an ArrayFormula to be used in Google Sheets. I have tried several formulas but have not had any luck getting it quite right.

I am using a google sheet to compile data from daily workout emails I receive. I then pull data from these cells to populate an RSS feed. I want to strip some things out of a cell to clean up the data that is pulled into my RSS feed.

Looking at B2 in the "Sample" tab, I have turned the text red that I'd like to match. The text in the cell will always follow this format with the URLs preceding that bit of text "Posted by Dan DeLomba..." I am assuming I could match on "Posted by" then remove everything preceding the word "Posted"?

Towards the bottom of B2 is the second part to be cleaned up. Again, I have turned the text red. In this case, I'd like to match "The Post" then remove "The Post" and everything following.  This would leave the middle bit of text to be pushed into my RSS feed.

I have created a sample sheet with 2 tabs.  The Sample tab is laid out like my actual sheet.  I have a second tab with some notes.  If anyone would be able to help me, that would be great!  Here is the link to the sample sheet.

Thanks!

Note:  User Lance Jacobs (NYC)  helped with this formula in a previous post :

=Arrayformula(Trim(IF(B2:B="",,Replace(RegexReplace(B2:B,"(^.+\n+.+\n+)",""),Find("The post",RegexReplace(B2:B,"(^.+\n+.+\n+)","")),10000,))))

but it wasn't quite right as it worked for row 2 but missed some bits in the next 2 rows.  He asked that I repost.

## Monday, September 17, 2018

### yogi_Split And Transpose Data In A2:C3 Per User Specification

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Sep-17-2018

# How do you SPLIT and TRANSPOSE data at the same time?

Hello friendly smart people,
I am trying to split and transpose data at the same time, and was hoping someone might be able to help me out here.
Thanks for any help

## Monday, September 10, 2018

### yogi_How To Allocate Tax Month Number For British Tax Year That Begins On Apr 6th

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Sep-10-2018

question by: Russ Walker 7

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI

question by: Jo Smith9

# How do I allocate a number to a month? (tax calendar run)

I'm finding this so tricky! if there are better ways to do this, I'm happy to consider it!

The Problem
The UK tax calendar runs from 6th April 2017 to 5th April 2018. It starts on the 6th of each month and ends on the 5th of the next month. TBH, I don't mind if your solution runs from 1st of the month to the end date of the month.

6th April 2017 to 5th May 2017 is Tax Month #1
6th May 2017 to 5th June 2017 is Tax Month #2
...
6th Dec 2017 to 5th Jan 2018 is Tax Month #9
...
6th Mar to 5th April 2018 is Tax month #12

I want to have a hidden column which would have the relevant tax month so that a Pivot Table could - in an ordered fashion - show the costs related to that month.

For Example
 04/04/2018 £ 8.64 WholsaleRUs Stock 04/04/2018 £ 5.50 Office Plus Stationary 13/12/2017 £ 2.50 WholsaleRUs Stock 13/02/2018 £ 6.98 MiniMart Stock

So looking at the above table, the first two rows should be Tax Month #12, the third would be tax month #9 and the last would be tax month 11.

Thank you!

## Sunday, September 9, 2018

### yogi_Tabulate Date For Specified Day of Every Month Unless That Day Falls On A Weekend

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Sep-09-2018

question by: Russ Walker 7
How to enter a date that has to be on a weekday.
Hi, I want to enter the 6th of the month into a cell unless the 6th falls on a weekend (saturday or Sunday) then I want the date in the cell to be the Friday before the 6th. The formula I have managed so far moves the date forwards to a Monday and not back to the Friday, is anyone good with date formulas. Col I is the col with the formula and col A is just for Col I to get the initial date from.  Any help will be greatley appreciated.

## Saturday, September 8, 2018

### yogi_Create Row By Row 'Group Membership" From Set Of 'E Mail Entries'

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Sep-08-2018

question by: Thomas LIOT
Regextract in third column if Col 1 and 2 empty. If col 3 empty too give a message.
Hi guys,

I can't solve this one alone.

This data comes from a google contacts CSV, and the goal is to add contacts to groups named as the value between "@" and "." of the email address... (Franck@google.fr goes into google group, Mike@other.com goes into "other" group

 Group Membership E-mail 1 - Type E-mail 1 - Value E-mail 2 - Type E-mail 2 - Value E-mail 3 - Type E-mail 3 - Value EXEMPLE WORK work-email-example@EXEMPLE.fr HOME home-email-example@home.fr OTHER Other-email-example@other.fr

If email one doesn't exist, create group from mail-type-2

 Group Membership E-mail 1 - Type E-mail 1 - Value E-mail 2 - Type E-mail 2 - Value E-mail 3 - Type E-mail 3 - Value HOME HOME home-email-example@home.fr OTHER Other-email-example@other.fr

And if email two doesn't exist either, get email 3

 Group Membership E-mail 1 - Type E-mail 1 - Value E-mail 2 - Type E-mail 2 - Value E-mail 3 - Type E-mail 3 - Value OTHER OTHER Other-email-example@other.fr

From this example, I can create the two first groups "WORK" and "HOME" but I have got #N/A on the third...

Here's the formula:

'=ArrayFormula(IFERROR(REGEXEXTRACT(AL2:AL;"@(.*)\.");
IF(ISBLANK(AL2:AL);REGEXEXTRACT(AN2:AN;"@(.*)\.");
IF(ISBLANK(AN2:AN;REGEXEXTRACT(AP2:AP;"@(.*)\."))))))

I have tried with operators AND / OR / NOT ... with no success. Also, what I wold like to do is to use the "iferror" to get a text such as "this contact doesnt have email addresses yet" and put that into a contact note in another column.

Not sure already, but I could also consider getting each group separated by comma (ie: work,home,other) if a contact as more than one email address but that's not mandatory at all.