Sunday, June 17, 2018

yogi_Unique Values From Multiple Columns Based On Values In Another Column

Google Spreadsheet   Post  #2463

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

question by: classroom in school
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/2AmNIytMxiw;context-place=forum/docs
Unique list of names from multiple columns and based on value in another column?
https://docs.google.com/spreadsheets/d/1vizdH7_Eb5E7f1aHZY6TX_P6xqdxOhqMked2us1EXFI/edit?usp=sharing

I have three columns:
A2:A6 contains letters like A or B
B2:B6 contains names
C2:C6 contains names

A name  can appear in both column B and C, like "joe" or "ivan".

How can I create a list with unique names, where the names only appear on the same row as letter B in A2:A6?


Saturday, June 16, 2018

yogi_Compile Monthly Statement By Item From Monthly Expenditure Sheets

Google Spreadsheet   Post  #2463

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

question by: Edison Wu
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/Cs2NyUuZVI0;context-place=mydiscussions
Data validation question
Hi all,

I want to make a budget spreadsheet. I made a data validation drop-down list of the items so I can choose, for instance: I choose "Grocery" from the drop-down list and enter the value next to it. 

But is it possible to store the value and output the value on another sheet. Like I put $25 next to "Grocery" on day 1, and put $10 on day 2. Is it possible to show the sum of the value on "Grocery" and output it?

Thanks a lot.

Regards,

Edison

yogi_Prepare A Consolidated List Of Certifications Earned From Data In 'Form Responses 1'

Google Spreadsheet   Post  #2462

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

question by: dlrbestpublic
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/79stcxty14o;context-place=mydiscussions

How to create an array that omits blanks and writes to one cell?

Need some help please. I am using a google form to input certification into a Google Sheets spreadsheet. The form answers get written into a worksheet named 'Form Responses 1'. That tab and my Master tab have a Member ID column for matching purposes. 

Right now, I have no problem copying the first matching line (using Index/Match) from the Form Responses 1 tab into the Master tab to populate that member's certifications. 

However, as I have a few folks that are not familiar with Excel/Google Sheets, I would like the Google Form to be used for certification updates as well -not just a one time entry per member. For example, on initial entry, Certifications 1 and 2 were recorded for Member 1. Then one month later, Member 1 received Certification 3. Using the form to input that adds another line to the Form Responses 1 tab for Member 1. I would like to create an array of all entries for Member 1 and for each cell that is not blank, write that information into one single cell on the Master tab. So far, I cannot figure out how to get it to disregard blanks and to write to the same cell. This is the base formula I have been working with: 

Column B has the Member ID in the one sheet and A3 has it in the other. Column J is just one of the 28 certifications that are being tracked. Let's call that Cert1. I am trying to write the responses found in J (knowing that all will be blank but 1) into cell I3 on the master sheet. I have tried using ISTEXT, <>"", isblank, but the answer is eluding me. Any help is most appreciated.

=ArrayFormula(IFERROR(INDEX('Form Responses 1'!J$2:J$999,If('Form Responses 1'!J$2:J$999<>"",(SMALL(IF('Form Responses 1'!$B$2:$B$999=$A3,ROW('Form Responses 1'!J$2:J$999)-MIN(ROW('Form Responses 1'!J$2:J$999))+1),COLUMNS(I3)))),""))

yogi_Compute Amount By Category By Month

Google Spreadsheet   Post  #2461

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

question by: Marc Zaldivar
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/sVyYglkxraI;context-place=forum/docs
Trying to set up multiple conditions with SUMIF or DSUM?
Hi,

I'm have a form that collects payments by different categories.  I'd like to create another sheet that shows sums for each of the categories within a certain date range.

For instance, Sheet1:Column A is Payee.  Sheet1:Column B is Date.  Sheet1:Column C is Amount. Sheet4: Column D is Category (let's say, CatA, CatB, CatC).
On Sheet 2, I'd like to sum any amounts that are between 5/1/2018 and 5/31/2018 in CatA on one row, CatB on another.  

I assume I need nested IF statements or a DSUM, but I keep getting an error. 

Thanks!

Z


Wednesday, June 13, 2018

yogi_Compute Percentage Of Persons Who Got Scores Between Specified Range Of Numbers

Google Spreadsheet   Post  #2460

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

question by: J_sj
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/mh-DoJ_Jljs;context-place=mydiscussions

Simple percentage-calculating. (arrayformula?)

Hello, dear friends of higher understanding. 

I actually have two problems, the first one is my real problem, and the other one is explaining the first problem. The title isn't great, I know. 
Okay, so heres the deal, I have a spreadsheet looking like this:

The rows 5-19 each contain the scores for different players on 10 questions (1-10, G:F). 1 row is one player. Simple, right?
Now, here's my problem: I would like to make some simple statictics, like how many of all players (In percentages) get between 0-5 points,  how many get 6-10  points etc on these 10 questions.
So I need to:
1. Add up the points from G:F for each row.
2. Check how many rows that get between 0-5 points, how many that get 6-10 etc.
3. Calculate how many percentages of all players that got between 0-5 etc.

At the moment, I'm using far to many cells for this. It is my belief that this can be done with few cells using arrayformulas or something, I just donät know how to. Does anybody know?

I hope I have made the problem easy to understand.

Have a great morning/day/night!


yogi_Sum Up Time Entered in B3:B As Minutes And Seconds Without The Colon Character For Ease Of Entering

Google Spreadsheet   Post  #2459

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

question by: JoLR
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/FQa7QUUWdSE;context-place=forum/docs
Custom Time Formatting Help

Hello! I work in film editorial. I'm looking for a way to enter a time in minutes and seconds, and have it display as "xx:xx" - I'd like to enter this value as either 4 consecutive digits (i.e. no colon), or as minutes.seconds (e.g. 03.21). The reason being, I'm entering a lot of data, and it's a hassle to have to enter a colon when I just want to keep my right hand on the numpad.

Is there a way I can create a formatting rule, so that entering "0210" displays as "02:10" and correctly registers and minutes and seconds? Because I also have a function that adds the sum of this column so I have a total duration at the bottom.

Please let me know if that's clear. Thank you for your help!

JoLR

Sunday, June 10, 2018

yogi_Create Top List From Table In A4:B7 With Scores And Names In Descending Order

Google Spreadsheet   Post  #2458

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

question by: Jesper_S
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/lvwDq5sPzfU;context-place=forum/docs

Creating a toplist

 

Good morning/day/evening!

I have a table "score"(A3:B7). In this table, I have the scores of four different people. From this I would like to create a toplist, looking like the one in the picture (A10:B14). 

I need a function that places all the people with the same scores beneath eachother, a.k.a. "John" in B11, "Jessie" in B12 and "Emma" in B13. How should I do?

I have managed to put John in B11 and Abby in B14 by using a combination of index() and large(). The problem I get, however, is that John gets put in B12 and B13 aswell, so, yeah, help?

Cheers!

//Jesper

yogi_query a column of single cells and output it to a column of merged cells

Google Spreadsheet   Post  #2457

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

question by: Blorty Blortson
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/R3Ku2m0BbT0;context-place=mydiscussions

How do I query a column of single cells and output it to a column of merged cells?

Hi 

everyone! 

I have am trying to import some data into merged cells (3 cell height). I need the merged cells because they are effectively labels for 3 rows. The problem I am having is that the range being queried is not merged so some data is being "hidden" in the merged cells; 1 cell appears and the other 2 are hidden by the merge. 

EX:
Source:

Data1
Data2
Data3
Data4

Current Output:

Data1


Data4


Desired Output:

Data1


Data2


Data3


Data4

My query is a standard =query(importrange("url","tab!range"),"select * where Col1 is not null")

I have been looking for about an hour but can't find anything of help. Is it possible to do what I am attempting? 

Thanks in advance! 

Edit: What I am hoping for is to have only one value per merged cell. So if A1:A3 and A4:A6 are merged cells, the data from the query would go into cells A1 and A4. 

Sunday, June 3, 2018

yogi_Pull Individula Stats For Entities From Data In Tabs Of Another Spreadsheet

Google Spreadsheet   Post  #2456

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

question by: Joan Tomines
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/Hgn2vUJiq-A;context-place=forum/docs
Grabbing of data from one sheet to another.

Hello, Experts!

I need your help again.

I am working on this sheet:
>>> ATAMAI tab of Copy of Team Stats (Joan) (Please see attached image).

What we are trying to achieve is, we need to pull up the data depending on the month (in column A13) and the name on row 14.

The data with the stats will be coming from this sheet:
>>> All the tabs in Copy of TRIBE ATAMAI (Joan)

Kindly check and advise.

Thank you in advance!

- Joan :)
image.png
26 KB

Tuesday, May 29, 2018

yogi_Compare Two lists and Flag row where data from list 2 appears in list 1

Google Spreadsheet   Post  #2455

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

question by: Mike Wilday (LAPU)
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/PP-mexvj3xk;context-place=mydiscussions

Compare Two lists and Flag row where data from list 2 appears in list 1.

yogi_Compute Row By Row Running Count Of General Service Numbers (column C) And Compute Specific Service Numbers (column D)

Google Spreadsheet   Post  #2454

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

question by: Logan Twidell
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/x7o-9ArDbEk;context-place=mydiscussions
Dynamic cell referencing within an array function

Saturday, May 26, 2018

yogi_Conditionally Format Cells In Column A Which Match The Entry In DropDown List In Cell G6 Or The Group Which They Belong To From Sheet2 - Part2

Google Spreadsheet   Post  #2453

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

question by: Krzysztof Czajka
https://productforums.google.com/forum/#!topic/docs/XGupzH6gBac;context-place=mydiscussions
Matching formula
Hi folks!
I try to create timetable and I want to create formula which will help students or them parents to find when they have a class or private lesson.
I will attach link below.
What I want exactly is, if I pick name from List in cell G6 I'd like to that name to be highlighted in all spreadsheet, in addition kid belongs to the different classes/groups, Latvian, Trolls etc and I'd like to be those groups highlighted as well when I pick child name.
Simplyfing if I pick name Amelia in G6 I'd like to highlighted that name and Troll group as she belong to that group as well.
I was able to use match formula, but once I picked name in G6 and it was matching with the name from A column it was highlithing name but in G6 not in A column.
and You can see on the other spread that Rayley is in the Trolls group
so I'd like Trolls and his name to be highlithed.
I hope You will be able to understand. I always try to find out everything on my own but this is difficult for me.
Thanks in advance.

Friday, May 25, 2018

yogi_Array Formula For Sum Of Hours By Student And Date For Entries In Separate Date Sheets

Google Spreadsheet   Post  #2452

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

question by: JP1128
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/IHm7O4C7X_k;context-place=forum/docs

SUMIFS in ArrayFormula?

https://docs.google.com/spreadsheets/d/1E5IJET7tgSw3zrgYuDJGMWnMknEytX1jknuphHXKfxU/edit?usp=sharing
Working on Students!

I'm having a problem with the Column D. I need to make it so that when the person's Attend becomes "Y" the sum of the person's hours in the 8/10! would be calculated and returned.

I tried this
=ArrayFormula(SUMIFS('8/10'!$I2:$I,'8/10'!$E2:$E,$A3:$A))

yogi_Conditionally Format Cells In Column A Which Math The Entry In DropDown List In Cell G6

Google Spreadsheet   Post  #2451

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

question by: Krzysztof Czajka
https://productforums.google.com/forum/#!topic/docs/XGupzH6gBac;context-place=mydiscussions
Matching formula
Hi folks!
I try to create timetable and I want to create formula which will help students or them parents to find when they have a class or private lesson.
I will attach link below.
What I want exactly is, if I pick name from List in cell G6 I'd like to that name to be highlighted in all spreadsheet, in addition kid belongs to the different classes/groups, Latvian, Trolls etc and I'd like to be those groups highlighted as well when I pick child name.
Simplyfing if I pick name Amelia in G6 I'd like to highlighted that name and Troll group as she belong to that group as well.
I was able to use match formula, but once I picked name in G6 and it was matching with the name from A column it was highlithing name but in G6 not in A column.
and You can see on the other spread that Rayley is in the Trolls group
so I'd like Trolls and his name to be highlithed.
I hope You will be able to understand. I always try to find out everything on my own but this is difficult for me.
Thanks in advance.

Thursday, May 24, 2018

yogi_Conditionally Format Arrival Date If Shipping Date Has Arrived Or Passed

Google Spreadsheet   Post  #2450

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

question by: UMuildABook
https://productforums.google.com/forum/#!topic/docs/XGupzH6gBac;context-place=mydiscussions
Conditional Formatting: Is Date X days from Today
I have a column of cells with dates that an order needs to arrive in the customer's hands by.  Next to this cell is the number of days it takes to ship to their location.  I would like the arrival date to be highlighted it is less than the number of ship days away from today.

yogi_Find minimum value in range of duplicates

Google Spreadsheet   Post  #2449

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

question by: Paul-Grant Van-Rooyen
https://productforums.google.com/forum/#!topic/docs/8iyQlLwxcxs;context-place=forum/docs

Find minimum value in range of dulicates

Hi there folk,

I am looking for a formula/query to run and find the minimum values in a range that contains duplicates (2 or more). 

The first range which is sorted is in A1:B14 with the required results in D1:E18 (formula required in Cell D2.

The first range which is sorted is in A19:B32 with the required results in D19:E26 (formula required in Cell D20.


Cheers


Wednesday, May 23, 2018

yogi_List Dates For Specified WeekDay Starting From A Given Day For Specified Number Of Weeks

Google Spreadsheet   Post  #2448

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

question by: Colin TheWilson
https://productforums.google.com/forum/#!topic/docs/8iyQlLwxcxs;context-place=forum/docs
Increment dates
Hello! I'd like to know a way to increment dates based on the start of the week.

For example, I want to make a series of column headings that say "Week of 5/1", "Week of 5/7", "Week of 5/14", "Week of 5/21", "Week of 5/28".

I'm going to do this on a large scale (A new set of headings every month for many years), so I want to have a function that would only take the month and would auto-generate the week start headings. 

Is something like this possible? Thanks!