yogi_Sort Entries In Column A (that begin with Numerics and then an alpha) By Numerics Then Letter

Google Spreadsheet   Post  #2136

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

question by argjunks:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/CEF_B1kgw6Y;context-place=forum/docs
Sort alphanumeric strings by number, then letter

Hello,

I have a series of cells:

1A

1B

57F

42J

42

1

1C

and I want to sort them by number (either one or two digits), then the following letter. If there is no letter after the number, I want it to come first, like such:

1

1A

1B

1C

42

42J

57F

I have this so far:

=SORT(SPLIT( UPPER(UNIQUE(FILTER(A22:A63,A22:A63<>""))) , "ABCDEFGHIJKLMNOPQRSTUVWXYZ " ), 1, TRUE)

but it is only spitting out the number in the cell, so it is giving me:

1

1

1

1

42

42

57

Please help! Thanks in advance

yogi_Convert Entries With Fractions Into Their Decimal Equivalents Rounded To Spec

Google Spreadsheet   Post  #2135

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

question by JRPennington:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/mF3XksBoDAk;context-place=forum/docs
Fractions and Decimals question in Google Sheets

I'm trying to create a spreadsheet that will help me generate a list of dimensions to build things to. What I need to do is be able to type a fraction in one cell and have it calculate a dimension into another cell. I have the calculation set up and works great when inputting numbers using decimals but does not work at all using fractions. Most of my inputs are in fractions to the 1/16" and sometimes 1/32" so converting them to decimals before putting them in the sheet is just a giant inconvenience. In excel I can input a fraction and it will use the decimal form in the calculation (while still showing the fraction) and it's so easy. Does Google Sheets not convert fractions to decimals for the purpose of calculation?

EX. I need to take off 3/32" from each input that I give. I need to be able to input dimensions such as 5 9/16" or 8 1/4" and have it calculate. Currently I am having to input as 5.5625 or 8.25 respectively and it takes too much time trying to figure out the decimals myself. Basically I would like to put in 5 9/16" and have it automatically convert it. Is this possible?

yogi_Count The Number Of Entries In Column A With The Stipulation That Items Beginning With A Number Are Counted That many Times While Ignoring Other Numbers

Google Spreadsheet   Post  #2134

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

question by Happy Pups Bed and Biscuit:

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

How do i add numbers from cells when the cell contains a number and text

I have 20 cells in one column.  i fill the cells in with names.  I am having the final formula count how many cells are filled in but sometimes i have to put a 2 and text in one box in which case I would like it to count that box twice, or 3 and count 3 times.  Any Ideas on how to do this?  The 7 at the bottom is counting the cells with something in them by the =COUNTA(A1:A8).  I would like the 9 cell to count the number of cells with stuff in them, plus where there is a number add it as that many instead of just one.  Please Help.

Polom

2 Mires

Mika

Harper

2 Sidelko

Milliken

Huntley

7

9

yogi_Count The Number Of Entries In Column A With The Stipulation That Items Beginning With A Number Are Counted That many Times

Google Spreadsheet   Post  #2133

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

question by Happy Pups Bed and Biscuit:

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

How do i add numbers from cells when the cell contains a number and text

 I have 20 cells in one column.  i fill the cells in with names.  I am having the final formula count how many cells are filled in but sometimes i have to put a 2 and text in one box in which case I would like it to count that box twice, or 3 and count 3 times.  Any Ideas on how to do this?  The 7 at the bottom is counting the cells with something in them by the =COUNTA(A1:A8).  I would like the 9 cell to count the number of cells with stuff in them, plus where there is a number add it as that many instead of just one.  Please Help.

Polom

2 Mires

Mika

Harper

2 Sidelko

Milliken

Huntley

7

9

yogi_Create Table Of Students With Incomplete Assignments (marked *)

Google Spreadsheet   Post  #2132

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

question by CHMS:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/6f5wCUNPNYc;context-place=forum/docs
Macro or formula to extract info from multiple tabs based on criteria into new tab?
Hi,

I work with a team of teachers and we use Google Sheets as our shared gradebook. We have multiple tabs (one for each class) where we record our grades. Not all of our students are in each class, so each tab has column A where the students names are listed and row 1 where the assignments are listed. We would like to use a macro or formula to look through all of the tabs for "*", which represents an incomplete assignment and return the student's name, the assignment title, and the tab (class) name to a new tab. This would allow us to have an always up-to-date list of work that students are behind in.

I've read about a lot of ideas that seem to work for only part of the problem. We would really appreciate help on this. 

Thanks,

Carla

yogi_Create A List Of Words By Combining Words From 3 Columns

Google Spreadsheet   Post  #2131

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

question by Guilherme Avila:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/6WLSmO1Aj0I;context-place=forum/docs
Combination of list of words - Build an Array
Hi,

I am trying to build an array with 3 columns:

Column A:

cat

dog

cow

Column B:

1

2

3

Column C:

A

B

C

I've found this topic here that explains how to do with 2 columns.

So with 2 columns, I have already done it. You can see in this public Spreadsheet Here.

With 2 columns, I am using this formula:

=ArrayFormula(TRANSPOSE(SPLIT( CONCATENATE( A1:A3 &TRANSPOSE( B1:B3 & CHAR(9))) ; CHAR(9))))

And my result is:

cat1

cat2

cat3

dog1

dog2

dog3

cow1

cow2

cow3

Fine. But I need help to go further in the formula and make it work with the third column.

I expect the end result to be like this:

cat1A

cat1B

cat1C

cat2A

cat2B

cat2C

cat3A

cat3B

cat3C

dog1A

dog1B

dog1C

dog2A ...

Thanks for your help.

Guilherme.

yogi_Include Total In Row Below QUERY Result

Google Spreadsheet   Post  #2130

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

question by Muhammad Nur Rahman:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!msg/docs/YeJqQRBIZGI/mTZlBT7DBgAJ;context-place=topic/docs/NM-mlqalAKg
include total in below after query
hello, i wanna ask about query, can i get total after using query?

https://docs.google.com/spreadsheets/d/1frS5pjYYbKRem16u4ENZTacrJ5wY-nQ3zrbkYNfXW20/edit#gid=754185893

here in sheet4, i wanna get resume of status including the blank and get the total of data (the data dynamic) so the category in status could be add more or reduce and iwant the total always come in after the data change in below, is there a way?