yogi_Get Unique List Of Entities And Their Respective Count From A Single Column List Of Entities

Google Spreadsheet   Post  #2105

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-06-2017

question by: jschram:

https://productforums.google.com/forum/#!topic/docs/9HfIjoM9cU8;context-place=forum/docs
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

Google Spreadsheet   Post  #2104

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-06-2017

question by: ASchwab:

https://productforums.google.com/forum/?visit_id=1-636193431124668080-3096751358&rd=1#!topic/docs/PWBgW9TKCp0;context-place=forum/docs

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

yogi_In Table Of Lesson Plan Compute Running Balance Of Amount Due By Student In DropDown List

Google Spreadsheet   Post  #2103

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-05-2017

question by: Lance Jacobs (NYC):

https://productforums.google.com/forum/?utm_medium=email&utm_source=footer#!msg/docs/Z_CBHPYQUww/NDPR2NEsDAAJ
Arrayformula to track Current Balance in Transaction Register

My sheet: 

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

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

yogi_From Table Of Dates And Visitors Create A Table Of 'Week Number' 'Unique Visitors' And 'Number Of Visits'

Google Spreadsheet   Post  #2102

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-02-2017

question by: DanielAsk:

https://productforums.google.com/forum/#!topic/docs/buqGkCOUOcA

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.

yogi_Compute Row Number Of Nth Negative Number Up From last Occupied Cell In Column A

Google Spreadsheet   Post  #2103

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-01-2017

question by: hilman.chung:

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

How to find cell in column with the 12th negative value from the bottom?
Hi,

I like to find the index of a cell within a column that is the 12th negative value from the bottom (last populated cell).

Can someone help me?

yogi_Convert Fraction To Percent

Google Spreadsheet   Post  #2102

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-01-2017

question by: Ms Drasby:

https://mail.google.com/mail/u/0/#inbox/1595c05266f8c196

yogi_Convert Fraction To Percent

yogi_Conditionally Format In Column B Top 3 Unique or Non-Unique Entries as noted

Google Spreadsheet   Post  #2101

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-01-2017

question by: Matthew Farquhar:

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

How do I custom format the 1st, 2nd, and 3rd highest number in a column?

I am running a small sweepstakes competition, using google sheets so everyone can access it. I want it to automatically update who is in 1st, 2nd and 3rd by changing the color of their cell. Placings are based on who has the highest amount of points. What is a way I can do this or the closest I can get to doing this?