yogi_Sort Data In A3:A in Ascending Order by #nnn

Google Spreadsheet   Post  #2072

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-10-2016

question by: Tackky:

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

Sorting Order When Numbers are Treated as Text

Is there a way through a formula, an array, a script, anything... To fix the behavior of sorting text when numbers are involved?  I'll show you what I mean.  I have a comic book checklist and the sorting is basing the order on the first number in a text string like this...

Amazing Spider-Man (2015-) #1	Have
Amazing Spider-Man (2015-) #1.1	Want!
Amazing Spider-Man (2015-) #1.2	Want!
Amazing Spider-Man (2015-) #1.3	Want!
Amazing Spider-Man (2015-) #10	Have
Amazing Spider-Man (2015-) #11	Have
Amazing Spider-Man (2015-) #12	Have
Amazing Spider-Man (2015-) #13	Want!
Amazing Spider-Man (2015-) #14	Have
Amazing Spider-Man (2015-) #2	Have
Amazing Spider-Man (2015-) #3	Have
Amazing Spider-Man (2015-) #4	Have
Amazing Spider-Man (2015-) #5	Have
Amazing Spider-Man (2015-) #6	Have
Amazing Spider-Man (2015-) #7	Have
Amazing Spider-Man (2015-) #8	Have
Amazing Spider-Man (2015-) #9	Have

Do you see how all the double digit numbers starting with a 1 (like #10, #11, etc) all come before #2?  I'm sure it would do the same thing with triple digit numbers, etc.  It would be nice if it treated the numbers as numbers even within the text strings.

I know I could just go in and manually edit them all to include a zero first or something like that, but i have TONS of entries I'd have to do it for.  It would be much nicer and cleaner to have it sort through a formula of some kind.  I'm already using a formula for sorting while ignoring the opening "A", "An" or "The" using this...

=ARRAYFORMULA(IF(LEFT(A2:A,2)="A ",RIGHT(A2:A,LEN(A2:A)-2),IF(LEFT(A2:A,3)= "An ",RIGHT(A2:A,LEN(A2:A)-3),IF(LEFT(A2:A,4)="The ",RIGHT(A2:A,LEN(A2:A)-4),A2:A))))

If there was a way to adapt that (or add something) for the number issue, that would be amazing! Thank you for any help! :)

yogi_From 'All depots' Sheet Pull Data For Each Depot Into Each Depot's Own Respective Sheet

Google Spreadsheet   Post  #2071

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-11-2016

question by: Jack Bayntun:

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

Sort spreadsheet rows into different sheets?

Hi all, I'm trying to sort a spreadsheet into different categories, and I would like it so that if a cell in a row contains one thing, it displays the row in another sheet, ie if the cell contains 'BH' then I would like it to be displayed in the sheet/page Bath (BH) which is visible at the bottom, or if it contains WS then to Wells (WS). I hope I've made that clear as it's quite hard to explain, I've attached a screenshot to show what I mean. I'm not sure if this is even possible but any help you can offer is much appreciated!

P.S. The screenshot is from the Sheets app on iPad, but I mostly use a Mac with Safari or Firefox, just to avoid any confusion!

---

Hi all, I'm trying to sort a spreadsheet into different categories, and I would like it so that if a cell in a row contains one thing, it displays the row in another sheet, ie if the cell contains 'BH' then I would like it to be displayed in the sheet/page Bath (BH) which is visible at the bottom, or if it contains WS then to Wells (WS). I hope I've made that clear as it's quite hard to explain, I've attached a screenshot to show what I mean. I'm not sure if this is even possible but any help you can offer is much appreciated!

P.S. The screenshot is from the Sheets app on iPad, but I mostly use a Mac with Safari or Firefox, just to avoid any confusion

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

yogi_Find Different Words And Their Respective Count In A LIST OF Phrases

Google Spreadsheet   Post  #2070

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-07-2016

question by: Manny Sousera:

https://app.vcita.com/app/conversations/8fdiszatknkool66?flow=Email_Action

I have a column with whole sentences in each cell. What I need is to count the frequency of each word in the column, and to display the results in a new column (in descending order of frequency). An example is: (row 1) The apple fell from the tree. (row 2) The dog chased after the cat. (row 3) The cat ran up the tree. The formula would show (in a new column): The - 6 Cat - 2 Tree -2 Chased - 1 ... Etc. Preferably there would be a way to include a list of stopwords (so that 'the, or, if', etc, were not included in the count). I would provide the stopwords, perhaps in a separate column within the spreadsheet. I am stumped - I have looked up and down the net for a solution, without luck.

yogi_Compute Row By Row Sum of Entries In Columns C To End Of Sheet

Google Spreadsheet   Post  #2069

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

question by: Ellen Lucas:

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

How can I get individual rows to automatically find the sum?

I set up a reading log minutes data collection for the students in my class.  The format is simple, I have their names on column A and then I add their time read on column, B, C, etc. 

Ideally, I would like to set up google sheets so each week when I include their weekly minutes the sheet document automatically calculates the sums in each rows for each student. My goal is to be open the document and see one student's yearly sum immediately.  I'd also like to avoid each having to go to "insert - function - sum" thousands of time during a school year. 

 

Thank you!! 

yogi_Solution For Using Query Function To Sum Values In A Column Containing Both Numeric And Textual Data

Google Spreadsheet   Post  #2068

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

question by: Lei_Mao:

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

Spread Sheet Query Error AVG_SUM_ONLY_NUMERIC
Hi All,

I'm trying to use query on spread sheet to group the sum value of a column, here is my query: =QUERY(Raw!1:1000,"select A,SUM(B) Group by (A)",-1)

however I got the alert: AVG_SUM_ONLY_NUMERIC and google doesn't return any value

Could you advise where am I wrong? do I need to reformat the data?

Here is the raw data: https://docs.google.com/spreadsheets/d/1jAgGKyXKJoTkd2g_Suyey0XISVE2Y4mI0Sft0l_5rbs/edit#gid=502618972

tab1: my query tab 2: raw data

Thanks!

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

yogi_Using INDIRECT Function With Multi Sheet QUERY

Google Spreadsheet   Post  #2067

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

question by: Elden Fenison:

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

Using the indirect function with multi-sheet query, syntax help
I have the following formula that works fine. It queries a handful of budget sheets looking for expenditures in a certain category.

=sort(query({Amazon!A2:F;Amex!A2:F;'Cash+'!A2:F;'Double Cash'!A2:F;'USB Checking'!A2:F},"Select Col1,Col2,Col6 where Col5 ='"&D1&"'"))

I would like to use the indirect function to refer to these sheets instead of spelling them out. I have cells on this sheet that contain the names of the other sheets that I want to query.

I just can't seem to get the syntax right.

I've tried this... and it doesn't work.

=sort(query(indirect(E2&”!A2:F”);indirect(E3&”!A2:F”);indirect(E4&”!A2:F”);indirect(E5&”!A2:F”);indirect(E6&”!A2:F”);indirect(E7&”!A2:F”),"Select Col1,Col2,Col6 where Col5 ='"&D1&"'"))

The sheets names I want to query are in cells E2:E7.

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

yogi_Given A Table Of Contacts Split Entries Into 'FirstName' 'Spouse FirstName' 'LastName'

Google Spreadsheet   Post  #2066

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-27-2016

question by: R123R123:

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

Split Couple and Single Names from same column

Hi, I have a large sheet consisting of names similar to as seen below, both married couples with the format "(Husband) and (Wife) Last Name" as well as single individuals "First Last". How can I split the married and single names up so that I achieve the desired outcome attached below?

Thank you!

Screen Shot 2016-05-27 at 1.57.33 PM.png

14 KB