Monday, September 17, 2018

yogi_Split And Transpose Data In A2:C3 Per User Specification

Google Spreadsheet   Post  #2513

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.
Here is the spreadsheet I am currently working on: https://docs.google.com/spreadsheets/d/1yFswW4AB4YNyMr8mISqJh7P7Ym5ZaiW8mEloXzA7E4o/edit?usp=sharing
Thanks for any help

Monday, September 10, 2018

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

Google Spreadsheet   Post  #2512

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

question by: Russ Walker 7
Google Spreadsheet   Post  #2511

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     
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.64WholsaleRUsStock
04/04/2018£ 5.50Office PlusStationary
13/12/2017£ 2.50WholsaleRUsStock
13/02/2018£ 6.98MiniMartStock

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

Google Spreadsheet   Post  #2511

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

question by: Russ Walker 7
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/MeIq1I8-QWg;context-place=forum/docs
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.
Capture.JPG



Saturday, September 8, 2018

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

Google Spreadsheet   Post  #2510

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

question by: Thomas LIOT
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/ki3DWMKgJrc;context-place=mydiscussions
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 MembershipE-mail 1 - TypeE-mail 1 - ValueE-mail 2 - TypeE-mail 2 - ValueE-mail 3 - TypeE-mail 3 - Value
EXEMPLEWORKwork-email-example@EXEMPLE.frHOMEhome-email-example@home.frOTHEROther-email-example@other.fr

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

Group MembershipE-mail 1 - TypeE-mail 1 - ValueE-mail 2 - TypeE-mail 2 - ValueE-mail 3 - TypeE-mail 3 - Value
HOME

HOMEhome-email-example@home.frOTHEROther-email-example@other.fr

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

Group MembershipE-mail 1 - TypeE-mail 1 - ValueE-mail 2 - TypeE-mail 2 - ValueE-mail 3 - TypeE-mail 3 - Value
OTHER



OTHEROther-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.

Thanks for your help guys, I have already learnt a lot reading subjects here.


yogi_Tabulate Letter Groups 'A' 'B' 'C' 'D' Etc Assess Groups 'B and C' 'C and D' 'D and E' And So On

Google Spreadsheet   Post  #2509

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

question by: Bram D Maat
https://productforums.google.com/forum/#!topic/docs/BMGttzjKSN8;context-place=forum/docs
IFS functions that adjust
Hello All,

I am a teacher, the students work in groups. Called Group A, B, C and D. The all create a lab report for an experiment.
I want the Groups to assess the work of other groups. 
Group A assesses Group B and C.
Groep B assesses Group C and D.
Group C assesses Group D and A.
Group D assesses Group A and B.

I can do this with IFS functions but the problem is this. See the example: 

The problem is this:
If I have more groups, let's say A,B,C,D and E then I want the function to understand that Group C needs to assess Group D and E instead of Group D an A.

I am not really clear how I set something like this up in a spreadsheet.
Hop to hear from you.

Bram


Friday, September 7, 2018

yogi_Rearrange Entries From 'Data Table' Of Open-Ended Number Of Columns And Rows In 'RowNo - Col No' And 'Values' Table

Google Spreadsheet   Post  #2508

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

question by: BPBC Tech
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/SiD3o67xGpk;context-place=mydiscussions
Calculating number of form submissions in Sheets for each individual
example.JPG
I am looking for help on how to query imported data, concatenate into a list in col1 and date sort by the values. Dataset example is the top table and the result i am looking for is the lower table. Thanks for any insight! 


Thursday, September 6, 2018

yogi_Rearrange Entries In 'Data Table' in 'RowNo - Col No' And 'Values' Table - 2

Google Spreadsheet   Post  #2507

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

question by: BPBC Tech
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/SiD3o67xGpk;context-place=mydiscussions
Calculating number of form submissions in Sheets for each individual
example.JPG
I am looking for help on how to query imported data, concatenate into a list in col1 and date sort by the values. Dataset example is the top table and the result i am looking for is the lower table. Thanks for any insight!