yogi_From Multiple Entries By Same Name In Column A Pull Data For First Instance Of Each Name

                                         Google Spreadsheet   Post  #1660
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-31-2014
post by T-Virus: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/VVVxNacfqYM)
Sheets Query return top 1 for each group

Hi, 

Is it possible to return the first and last value also rater then a min max grouped by Column. 

I just want to return the first value in the array not the minimal one. 

Thanks 

---

Here is an example: doc

It should return the first value depending on the date given by Name

------------------------------------------------------------------------------------------------------------------------------------------

yogi_Compute Row By Row Number Of Days of Activity By Specified Client During a Speified Month

                                         Google Spreadsheet   Post  #1659
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-31-2014
post by romgl -- question by Polish user: (https://productforums.google.com/forum/#!mydiscussions/docs/O9TCwKC5fPs)

Hi Yogi,

Thanks for your reply. I will rephrase my question:

I am trying to find a formula, that can help me count the number of days a client was active in a chosen month, as shown in the test spreadsheet with the link below :

https://docs.google.com/spreadsheets/d/1vSUchCgbvTg7rC6S76GVtashzN0tZpE1lcHcUy7INoU/edit?usp=sharing

Thus, for example, in the link above, on the Client activity sheet, if I choose February, I should see the following results :

Client A   2

Client B   2

Client C   0

But if I select April, I should see the following :

Client A   1

Client B   0

Client C   0

Thanks again for your efforts in helping me with this.

------------------------------------------------------------------------------------------------------------------------------------------------

yogi_Single Formula In Cell A1 Of Row 1 For Column By Column Sum Of Multiple Columns

                                         Google Spreadsheet   Post  #1658
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-31-2014
post by Kris Pet: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/U7eIiElRx3U)
SUMMING MULTIPLE INDIVIDUAL COLUMNS

How do I sum multiple columns in an individual fashion without doing it 100 times?

Ex:I have 100 columns of data and I want the sum of each column.  Thanks.

----------------------------------------------------------------------------------------------------------------------------------------------

yogi_Compute Class Size Value Based On Table Of Number Of Students In Class And Class Size

                                         Google Spreadsheet   Post  #1657
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-31-2014
post by Steve Brantley: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/Tt4Tzr2_xtc)
need formula: if cell value = range 1 then input a, if range 2 then input b, etc.

Hi, 

I am recording the number of students attending a classes in a spreadsheet. That number ranges anywhere from 2 to more than 100.  I want to have another column called class size in which the number of students is labeled a size 1-5 based on the number of students who attended. The ranges would be 1-9, 10-19, 20-29, 30-39, 40 + . I'm looking for a formula that would take the number of students and output the class size. for example cell m24 has a value of 33 and I want the "class size" value '4' output into cell n24.

---------------------------------------------------------------------------------------------------------------------------------------------

yogi_Formula For Cell E13 to house 'B' If D13 less than 5 -- 'S' if D13 greater than 5 -- 'G' if D13 equals +5 or -5

                                         Google Spreadsheet   Post  #1656
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-31-2014
post by Jos P: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/imUDfhEbSQE)
Conditional formatting adding color and letters to a cell based on another cells
I want to have cells change color and add a letter to that cell based on the value of another cell. Im using cell D13 for numeric ranging from 200 to -200, in cell E13 Im running custom formulas that change the cell E13 to red, blue or grey based on the values of D13. "=D13<5" =blue in Range E13, and "=D13>5" =red in range E13. I want to also have it put a B in the blue ones an S in the red one, grey in the -/+ 5 ones. Not sure how to add the B/S/G values to it.
---
Here is the a sample, the first tab, Daily closes. D13 and E13.

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

Thanks

--------------------------------------------------------------------------------------------------------------------------------------------------------

yogi_From A Table Of Products By Name Create A Table Of Names By Product

                                         Google Spreadsheet   Post  #1655
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-31-2014
post by Sargon Aldent: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/Hk8kNPU_PV8)
I am trying to create several lists of names that are based on abilities from a table. The table is in rows like this:
A                 B                  C                   D                   E
1  Lisa        COOKS      CLEANS      DRIVES          SEWS
2 Linda       COOKS      CLEANS          na                   na

For example:

Lisa    Cooks, Cleans, Drives, and Sews.
David Cooks, cleans
Linda Drives
Sue Cooks and Sews


Cooks List:
Lisa
David
Sue

Cleans list
Lisa
David
Sue

Drives list
Lisa
Linda



Tried using Hlookup but the value returned is text and always get an error because Hlookup expects the result to be  a number. Does anyone have a simple solution or function I can use to do this??

Thanks
---
Hi, 
https://docs.google.com/spreadsheets/d/1arfY8JNggWMLu5w0UowDJqzgRLJutQlmAJHsaYzXvSs/edit?usp=sharing

This is a example of what I was trying to do, but without the errors in the list.

Thanks,
-----------------------------------------------------------------------------------------------------------------------------------------------

yogi_Find The Title Of Column(s) (in row 1) For The Maximum Value(s) In Row 2

                                         Google Spreadsheet   Post  #1654
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-30-2014
post by HellWatcher: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/waWuUEs9GiY)
Help With Formula

Hello. I am trying to firgure out how to find the max value between 3 columns and return the title of that column.

Example:

a  b  c

1  2  3

In that example I would want a cell to show me c not the number 3.

---

I can do it with a long series of IF statements but if anyone has a suggestion on something shorter and easier to mange, that would be awesome.

=IF ( AND ( K6 > L6 , K6 > M6 ) , "A" , IF ( AND ( L6 > K6 , L6 > M6 ) , "B" , IF ( AND ( M6 > K6 , M6 > L6 ) , "C" , IF ( AND ( K6 = L6 , K6 =M6 ) , "A & B & C" , IF ( K6 = M6 , "A & C" , IF ( L6 = M6 , "B & C" , IF ( K6 = L6 , "A & B" , "ERROR" )))))))

---

I also need it to skip any zero values.
-----------------------------------------------------------------------------------------------------------------------------------------------

yogi_Compute Row By Row Number Of Days of Activity By Specified Client During a Given Month

                                         Google Spreadsheet   Post  #1653
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-29-2014
post by romgl -- question by polish user: (https://productforums.google.com/forum/#!mydiscussions/docs/O9TCwKC5fPs)
How to list and count unique cells in multiple columns?
Hi,

I have been trying to use the COUNTUNIQUE formula along with the FILTER formula, for a set of data, where I am trying to count the unique dates in a month on which activity has taken place with certain clients. While using the COUNTUNIQUE formula with FILTER, I notice that the output shows 1, even when there was no activity with a client in a given month.

Can anyone please help me? :)

---

Hi Yogi,

I did wrap the expression in IFERROR. Please find below the link :

https://docs.google.com/spreadsheets/d/1vSUchCgbvTg7rC6S76GVtashzN0tZpE1lcHcUy7INoU/edit?usp=sharing

In the file you will notice, there are 3 clients, Client A, Client B and Client C, with their own dashboard with details regarding the service offered to them. Client A and Client B use the service on 2 days in the month of February. Client C does not use the service at all yet.

In the Client Activity sheet, when I try to calculate the no. of days the 3 clients were active in the month of February, using the formula IFERROR along with COUNTUNIQUE and FILTER, I get a result ( which can be viewed on the shared spreadsheet) different from the expected result, which was to be : 

Client A    2

Client B    2

Client C    0

Apart from the above, in the same shared spreadsheet, I am facing another problem, regarding copying data automatically with the help of a formula, from the Transactions Log into the respective Client's dashboard.But I was wondering if I am able to post the problem here, since it is not directly relevant to the subject of the post. Presently I have been referencing cells manually.

I would be very grateful if you are able to assist with these problems.

Thanks for this initiative.

-----------------------------------------------------------------------------------------------------------------------------------------

yogi_Pull From Sheet1 Entries In Columns A To C And Put Them All In One Row

                                         Google Spreadsheet   Post  #1652
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-28-2014
post by j.kalintyler: (https://productforums.google.com/forum/#!mydiscussions/docs/joMoaBhmGVU)
Columns of data paste in all one row.
Going from new GD sheet to an old GD sheet, pasting (for instance) three columns and five rows comes out as one long row. Hoping that the data posts correctly, so for example:

y	Daniel	Frey
y	Kevin	Mukai
y	Harry	Chawla

Pastes into:

y

Daniel

Frey

y

Kevin

Mukai

y

Harry

Chawla

Fun.

Any ideas?

Also, re-creating a couple hundred old GDs into new GDs is not an option. 
--------------------------------------------------------------------------------------------------------------------------------
I had posted a formula in the forum ... following is an illustration for solution to a bit more generalized problem

yogi_Using Timestamp Column Compute Number Of Transactions For Each Day

                                         Google Spreadsheet   Post  #1651
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-27-2014
post by Thom Duncan: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/3wX7Mj5TRtw)
Ignore or delete time stamp in date fields

'd like to see a count of how many applications I receive each day, which I can do using a pivot table.

However, the value reported includes both the date and time, and I need to either delete the time stamp or ignore it.

Here's some sample data:

5/1/2013 1:52:00

5/1/2013 14:41:00

5/1/2013 21:57:00

5/2/2013 21:58:00

5/5/2013 13:07:00

5/6/2013 16:41:00

5/9/2013 18:50:00

I tried doing a search and replace for any values after 2013, which turned the time stamp to " 0:00:00", but I'd like to know if there's an easier solution that eliminates or ignores those values. Choosing to display just the date does not solve this issue.

Thanks!

----------------------------------------------------------------------------------------------

yogi_Use A Computed Column Formula In Cell of A Frozen Row So The Range Can Be Sorted With Formula Working

                                         Google Spreadsheet   Post  #1650
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-27-2014
post by Shaun Fordham: (https://productforums.google.com/forum/#!mydiscussions/docs/Bcdc7CzA-GY)
Help with array formula positioning

Hello, everyone!

I am needing some help re-positioning a function into another cell and still have it work. I will start with a screen shot and the syntax:

The function is currently in D3 and reads as follows:

=ArrayFormula(if(K3:K<>"","Received",if(J3:J<>"","ROES'd",if(I3:I<>"","Approved",if(H3:H<>"","Emailed",if(G3:G<>"","Arranged",if(F3:F<>"","Exported",if(E3:E<>"","Edited",))))))))

Essentially, if anything is filled into the blue columns (in this case, initials and dates), the D (purple) column will auto-populate with the most recent (right-most) step. This is an amazing function I got with the help from someone on this board. There is a slight hiccup, however.

As you can see, there are 2 frozen rows and 4 frozen columns. I am constantly sorting the rows in different ways. D3 (the cell with the function) currently has Oakleaf HS on the row. If I re-sort it, D3—along with the function—will appear elsewhere and any D cell above the Oakleaf row will have a blank cell because the array formula will not read those. This is a problem.

I would like there to be a way for this function to work regardless of how it's sorted, so ideally in the D2 (purple) cell. How can I rewrite this function to make that work? I've tried simply copy/pasting it, but it only pulls from a row down and it's out of alignment. Is there a way to keep this function in the frozen section so it still works whilst keeping the word STATUS in D2? Any help would be greatly appreciated. Thanks!

Shaun

---

Here is a mock spreadsheet I made. The same cells apply as the sceenshot.

https://docs.google.com/spreadsheets/d/1tw7IjarjZ3Hbn7x2t46zDb0GgU1bBvkDTpAK5xv05FA/edit#gid=0

---------------------------------------------------------------------------------------------------------------------------------------------------