Sunday, January 15, 2017

yogi_Group Total From Table Of Entities And their Corresponding Amounts

Google Spreadsheet   Post  #2110
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-14-2017
question by tiledude:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/qNW8dJag7PM;context-place=forum/docs
Arrays and adding columns of data

Hello,

This formula works fine and is giving the correct prices based on AW. 

I am trying to add the data from AW.

basically trying to add another column of data.

=QUERY('Copy of 01-FBA'!4:1000, "select SUM(R) GROUP BY AW label SUM(R) 'R'")

thanks for any help :)


RAW
14.99data from AW
439.82data from AW
1169.52data from AW
299.86data from AW
194.91data from AW
14.99data from AW
169.9data from AW
209.9data from AW
49.98data from AW
39.98data from AW
304.83data from AW
174.93data from AW
144.93data from AW
99.96data from AW

Saturday, January 14, 2017

yogi_Reverse Entries In Sheet Named Data Horizontally (except for entries in first column)

Google Spreadsheet   Post  #2109
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-14-2017
question by PY_:
https://productforums.google.com/forum/#!topic/docs/CZjRNAgMyUg;context-place=mydiscussions
Reverse data horizontally
I have a massive spreadsheet of data that can grow horizontally and vertically. What I'm trying to figure out how to do is duplicare that data on another sheet only in reverse order horizontally using formulas or a script starting at column B. Is there a formula or script I can use to accomplish this? Keep in mind that the column and row numbers change so the formula/script needs to be able to change with it.

yogi_What Words Occur Most Often In Range A:A And Their Corresponding Frequency

Google Spreadsheet   Post  #2108
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-14-2017
question by hlopez_:
https://productforums.google.com/forum/#!topic/docs/T2fqkh1OHHU;context-place=forum/docs
Count every word in column and display word and frequency
I have a column of 4000 entries. Each entry has a sentence. I would like to know what words occur most often in the A1:A range and their corresponding frequency

Wednesday, January 11, 2017

yogi_Add Specified Text To Range Of Cells Containing Phone Numbers

Google Spreadsheet   Post  #2107
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-11-2017
question by DrNasef OrthClinic:
https://productforums.google.com/forum/#!topic/docs/DuLlELkK_PI;context-place=mydiscussions
How to add specific text to range of cells containing cellular phone numbers?
Hello I was trying to edit my exported google contacts as a google spread sheet and edit the telephone numbers, unify them, and delete the duplicates. 
I noticed almost all my numbers lack the starting digit "0" for cellular numbers. (Proper format being : 0XXXXXXXXXX)
I would like to add the following : "+20" to all these numbers in a certain column

So say I would like to add "+20" to all cells in column AL from AL1 till AL3480 
Is there a "formula " for that ?
Thank you and naturally any help is highly appreciated

P.S
If anyone could also point out how to erase the duplicates in the easiest possible way I would be very grateful. It is a real pain sifting through almost 4000 contacts!!

Sunday, January 8, 2017

yogi_Sort Inventory In Column A By Article Size In Ascending Order (not all articles have size embedded at the end of article name)

Google Spreadsheet   Post  #2106
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-08-2017
question by Tammy Ellis:
https://productforums.google.com/forum/#!topic/docs/jnlSJLvgts8;context-place=forum/docs
Looking for help with adding a filters by last words in a row
I have an inventory spreadsheet and would like to be able to filter based on size of inventory.  I attached a copy of the Drive spreadsheet and would like to filter the first column by e.g. 2-4, 6-8, Petite, Plus.  Each size has a print name in front (e.g Blue Canyon 2-4, Blue Canyon 6-8)...I'm a novice....any help would be appreciated. 
Jan 6 Inventory copy.ods
14 KB
Jan 6 Inventory copy.ods

Friday, January 6, 2017

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