yogi_Split A Master List Into Several Parts By Specified Group Of Letters Such As A-F G-M And N-Z

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #664   Jul 30, 2012     www.energyefficientbuild.com.


user PriestessMars said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/la7WxKGBM4Q)
Splitting a Master List into three separate lists based on letter of alphabet
I have a Master List, filled with 200 or so names.  Is there a way to separate that list into three smaller lists by last name, (for example, Sheet 1 would contain last names that beginning with the letters A-F, Sheet 2 G-M, and Sheet 3 N-Z) where the lists would be updated as the master list is updated, without doing it manually?
------------------------------------------------------------------------------------------
following is a solution to the problem

yogi_Extract Range Of Cells Covering Last Occupied Row And Column Of Sheet1 Into Sheet2 So That Other Cells Of Sheet2 Can Be Manually Edited

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #663   Jul 30, 2012     www.energyefficientbuild.com.


user PBD Records said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/usiaRKVatTU)
Adding all data from one column to another column on seperate spreadsheet

Hi i'm going to try to explain this as best as I can and please excuse my phrasing I am a total spreadsheet noob :)
I have 2 sheets "Sheet 1" and "Sheet 2". In Sheet 1 column A as shown below I have multiple values under the header "Product #" and also some blank rows. I need an equation that will transfer all the non blank values from sheet 1, column A to Sheet 2 column A. The most important part though is I need this equation to work no matter how many values I have on Sheet 1. I also need the blank rows of sheet 2 left after the transfer to be manually editable (so in this example A7 and below). For example if I wanted to manually add the value Z80 to A7 on sheet 2
           Sheet 1                 Sheet 2
         A         B       ____  A         B       
1|  Product # |            1|  Product # |                                  
2|      Z50   |            2|            |
3|      Z51   |            3|            |
4|      Z52   |            4|            |
5|      Z53   |            5|            |
6|      Z54   |            6|            |
7|            |            7|            |
8|            |            8|            |
9|            |            9|            |
I left an example spreadsheet that might be easier to visualize than my representation above.
Thanks so much in advance for any and all help!

Attachments (1)

Example.xls

11 KB   View   Download

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

following is a solution to a bit more generalized problem

yogi_Rearrange Data By Entities In Column B C D And Set Of Specified Number of Columns

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #662   Jul 30, 2012     www.energyefficientbuild.com.


user aleksialkio said: (http://productforums.google.com/forum/#!searchin/docs/aleksi/docs/n4xP-jmDtXQ/KBmKsp4Y5XkJ)
Using query to reorganize the player details
Hi,

I'm trying to organize the spreadsheet to a new way. I'm wondering is the query function able to do this and I have tried to think it with many ways but I do need some help. Perhaps I'm thinking too difficult :-)

I have a form that collect the information from one team to one row. I need better way to show them and I will make individual sheets for every age group to publish. I made an example sheet what I need to do.

https://docs.google.com/spreadsheet/pub?key=0AkMVQyM7UcFddGg0SG56LTFlRVNZZTVIZGNzUHFJLVE&output=html

Thanks, Aleksi

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

following is a solution to the problem

yogi_Arrange Multiconditional Sum By Month Customer And Product Size As Delineated By User

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #661   Jul 29, 2012     www.energyefficientbuild.com.


user urbis said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/rMjbFiShFoA)
How to transfer data 
I ask for help, how to make the table ENTER to transfer data into a table TABLE.
Signs named (Steve, Marc, Bill) by format (10500x795, 1030x790, 525x459) I transferred to the month in which the table is in the months to ENTER
https://docs.google.com/spreadsheet/ccc?key=0Anv13BZwny-VdER6QU1OOFdrZ1NWVjFuLThGMWM0Nmc
------------------------------------------------------------------------ 
following is a solution to the problem

yogi_Compute Current Balance Row By Row Where A String Is Part of Entries Of Name Column In Another Sheet

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #661   Jul 29, 2012     www.energyefficientbuild.com.


user MysticEve said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/IcxyKBKF_Ds)
How to use VLOOKUP where the search criterion is only part of the text string
Goal: look up user's name and return their point balance.

Problem: some users have multiple user names listed within the same cell.

So far I know that using =VLOOKUP(A1;Sheet1!$A$1:$D$11;2;false) works for Single usernames. However I have cells that has multiple usernames for the same person recorded within the same cell. How do i modify VLOOKUP to search within the cell?

Spreadsheet can be found here:

https://docs.google.com/spreadsheet/ccc?key=0Alu6EuuqZZhVdGFQY1RpRG1HcWRZb2NzTEl1UFNnTnc

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

following is a solution to the problem

yogi_Set Up A Leaderboard That Stays Current As Scoreds Are Updated

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #660   Jul 28, 2012     www.energyefficientbuild.com.


user Yoshiman03 said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/oL--36yzuB0)
Vlookup with sorting?
Basically I have a table, the table contains a list of names, their scores for 6 weeks (1 week per column) and column for their total score. What I want to do, is have seperately but on the same sheet is a leader board, that will sort and list the players based on their scores.

Here's the table thus far, each of the scores in this sheet actually are a lookup from another sheet within the spreadsheet so its not just the value

https://docs.google.com/spreadsheet/ccc?key=0AhfbVrt9rspudDExZGczWFcyQmYtR0ZWUkZlOXNWMXc

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

following is a solution to the problem
I have laid it out in such a way as to allow adding more weekly data to the right of column L, and more names at the bottom of column F

yogi_Count Instances Of Strings In A Column And Compute Net Difference By Item

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #659   Jul 27, 2012     www.energyefficientbuild.com.


user Jabus said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/9naciXcSJcY)
Spreadsheet - How to Compare Two String Columns and Add New Rows From Forms?
Hi there,

I'm creating a bit of an awkward spreadsheet, it's going to end up having 2 columns, 1 with the name of the product and the second with the words add, remove or disregard. An example of the spreadsheet:

  A            B

Printer   ||  Add 1
Printer   ||  Add 1

Printer   ||  Remove 1

Computer  ||  Add 1

Computer  ||  Add 1

Computer  ||  Add 1

Computer  ||  Remove 1

I want to get a formula that will compare grab the name of the product in column A and then count how many "Add 1"'s there are and subtract how many "Remove 1"'s. So the end result should show a total tally of Printers = 1 and Computers = 2

Note the following may hurt to read: I feel like I'm going about this a really long way around but I don't recall seeing a function, or perhaps my brain just is over complicating things. Because what I ended up doing is creating 4 new columns the first being an IF statement that generates a 1 if column B says "Add 1" and a 5 (random number) if false. Then the same thing for the second column (colmn D now) where I do an IF statement to see if the item is a Printer or not giving 1 or 0 as the true or false. Then my third column does an IF to check it column C is a 1 and column D is a 1 then that amount of add printers is 1 (which i then sum) then same thing for removing printers but I use the the 5 in the column C to add to the 1 from column D (confirming its a printer) and so that if 5+1 = 6 then it's a 1 for the remove column and then sum that up then the the sum of column e is subtracted from d to get the right answers.

But that's a horrible solution, anyone have an idea on how I can compare the two columns? and then have new formulas form as people respond to a form? I feel like I'm doing such a poor job of this.

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

let us have a look at the following solution to the problem

yogi_Keep A Running Count Of Monitored Calls For A Specified Agent By Month

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #658   Jul 27, 2012     www.energyefficientbuild.com.


user AnswerFirst said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/dkRx3tKpfww)
How to calculate the number of items in one column dependent on values in an another column (similar to =arrayformula)
This is way too hard to explain without just sharing the sheet:
https://docs.google.com/spreadsheet/ccc?key=0AliYViHYAwaNdGRBb1JxOHo5REJlMHl6OU5Ld3VId2cI need to auto-populate column B with the cumulative number of calls that an agent (column F) has made in the month (column A) ... I found this formula that seems to work for any given row (i.e. this would work for row 2): 
=arrayformula(sum((A:A=6)*(F:F="ACM - Alaina McGinnis"))) 
but I can't figure out how to make this work for all rows without manually rewriting the formula in each row.  
It doesn't matter if this is solved with a formula or a script but I need the value to be calculated each time the form is submitted.  Thoughts, suggestions?  Thank you!
----
Thanks yogia,
To answer your questions:
1. The data is realistic; this is the exact sheet and form we use in production.
2 & 3. The calculation that I'm struggling with is on Sheet1: in column B I would like to calculate the total number of monitored calls that an agent (column F) has had for any given month (column A)  So, if Jane Doe (column F) was monitored on 3 calls in the month of July (represented by a 7 in column A) then the value in column B would be 3.  I've manually filled in the values for B2:B10 to show you what I would expect to see there.  Cell B11 has been left blank
4. I'm not sure a separate summary table would work for this as I need the value in column B to populated automatically as rows are added to this sheet.  This spreadsheet is populated based on the results of a form (which is visible to the public in case you want to glance at it.)
Thank you for your help and time!  :)
---------------------------------------------------------------------------------------------
following is a solution to the problem

yogi_Compute Stats For Specified Entities For Data In A Table For Variable Sheet Names In Another Sheet

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #657   Jul 27, 2012     www.energyefficientbuild.com.


user claire graham said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/HG_OXeI_tAM)
How do you find an average using data from 2 separate sheets? 

Hi,

I am working on a Spreadsheet (populated by a form).

I need to generate a sheet (named Analysis) that shows the average age of the users, this information is currently on 2 other separate sheets(named NAM and NPM).

The age information is shown in column I on both sheets.

I entered the following

=AVERAGE('NAM'!I3:I20000;'NPM'!I3:I20000)

which appears to be fine when there is no data, however, when data is entered the following error appears

error: Circular dependency detected.

I also need to show average ages for particular groups, (average male age, female age).

I have been successful in showing averages from data on just one sheet but combining 2 is causing me a headache.

All help would be appreciated.

with thanks Claire

----

Hi Yogia,

Thanks for getting back to me,

Here is the link to the Spreadsheet.

https://docs.google.com/spreadsheet/ccc?key=0ArtzSc59kmb2dEF1ZnNRMjJBc1BWSE9heW9hMXdqOWc

I am concerned with the Sheet named 'Nursery Analysis'.  This needs to reference several other sheets.

Initially I am concerned with Cell B9 on Nursery Analysis.

I would like to find the average age (in months) of all the boys who have completed a Baseline.

This information is found on sheets - BaselineNAM13/14 and BaselineNPM13/14 in columns G (male) columns I (age in months).

Currently there should be 8 male Baseline entries and the average age across both sheets should be 38.5.

This showing cohort information across 2 classes of nursery children.

I hope I have included all the information you require.

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

following is a solution to the problem

yogi_Count Submissions By Every Month Of The Year Assigning Value of 0 For Month With No Submissions

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #656   Jul 25, 2012     www.energyefficientbuild.com.


this is in response to a question by use Nieboy1 in http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/4cRXTQh4ml8

yogi_For Entries In Range B2:M7 Show Duplicated Entries In B12:M17

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #655   Jul 24, 2012     www.energyefficientbuild.com.


user TimeyMaster said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/ROCiXzPNKs0)
Looking for and Displaying Duplacuats

Target List Target1 Target2 Target3 Target4 Target5 Target6 Target7 Target8 Target9 Target10 Target11 Target12

Primary Target 372:562 370:561 372:574 380:558 380:562 385:575 366:561 375:557 380:576 381:576 390:560 383:567

Decoy 1 Target 378:562 389:571 390:560 390:555 392:561 386:572 386:549 380:568 371:548 371:549 381:548 381:544

Decoy 2 Target 382:571 373:564 386:553 385:572 385:574 388:570 358:546 357:546 381:558 377:559 379:561 377:564

Decoy 3 Target 386:549 383:574 381:575 381:576 388:538 380:578 376:565 371:567 372:567 376:568 384:568 384:567

Decoy 4 Target 380:579 379:579 378:571 378:559 374:564 380:565 388:569 388:572 387:572 387:573 386:573 386:574

Decoy 5 Target 374:571 367:573 373:565 388:588 375:571 376:569 384:574 376:575 384:575 385:579 379:575 379:574

the two cords marked in red are duplicate i was hoping to have these highlighted or displayed in some way so if i type two in like this it is easy seen so i don't make the mistake and hand out two of the same cords in a battle is this enouth info or do you need more?

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

following is a solution to the problem

yogi_Set Up Computed Columns For Row By Row Computations In A Form And Also A Grand Total

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #654   Jul 24, 2012     www.energyefficientbuild.com.


user Amber0803 said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/KEKsqI3M9kY)
Last row does not populate calculations after form submission, countif formula
Hi All,
I know this has been addressed before but I can't seem to find an answer that fits my needs, that I can understand anyway.
I have a form that basically calculates commission on certain items, they are set up as checkboxes so all of the items that are checked
go into the same cell. To calculate how much each is worth I have a column for each item and I use a COUNTIF formula to search that one cell (the E column) for
specific text. My problem is that for each form submission, the formulas don't copy down. I know I need to use an array formula but I can't get it to
work. I've also read that I can't use a COUNTIF formula in an array and that's where I get completely lost...
I have formulas in columns G:M that I need to repeat
These are a few examples of the formulas I am currently using:
=IF(COUNTIF(E2,"*Super*")>0,COUNTIF(E2,"*Super*")*4,"")
=IF(COUNTIF(E2,"*16*")>0,COUNTIF(E2,"*16*")*2,"")
=IF(COUNTIF(E2,"*Pineapple*")>0,COUNTIF(E2,"*Pineapple*")*1,""
Hoping someone out there can help me, I've been banging my head against my desk for hours...
----
Hi Yogi,
Here's a sample sheet/form:
https://docs.google.com/spreadsheet/ccc?key=0AkzKlHQOFyfDdDZIUWJXV3NYR05iVzhvM3M3NW45S1E
The form populates columns A-F, then the rest of the columns are calculations.
The formulas for the calculations are correct in the first two rows (which had to be manually put in after a form submission) but when a new submission is entered, like the last row,
the formulas do not appear. Each calculation column searches column E for a certain criteria then multiplies it by the correct number. At the end, the bonus column adds 1 if columns
H and I are on a single order. And then the total adds the entire row.  
Thanks in advance for your help!
---------------------------------------------------------------------------------------------
following is a solution to the problem

yogi_Generate A List Of Numbers Given Starting And Ending Sequence As Specific Numbers Or Via Range Names

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #653   Jul 24, 2012     www.energyefficientbuild.com.


user Chrisnetika said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/Mji-S-FK89I)
Creating Array To list a range of numbers
Im trying to list specied range as an array.
Basically I have 2 cells for example A1 and A2, A1 countains number 23 and A2 contains 44
I want to build an array so that in a column each row has number from that range.. for example
               A          
1             23              
2             44
3
4      =Arrayformula(A1:A2)
5             23
6             24
7             25
8             26
9             ... and so on till 44
--------------------------------------------------------------------------------------------------------
following is a solution to the problem

yogi_Set Up A Spreadsheet For Row By Row MultiConditional Output

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #652   Jul 24, 2012     www.energyefficientbuild.com.


user ularnaga said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/XaTChzo5SKs)
Multiple outcome if cell varies

Hello!

I have a calculation and i want to have multiple outcome.

So if the cell P3 has a value of "1 to 49" or "50 to 299" or "300 to 499" or "500 to 9999" then it will use one of the 4 different calculations below. 

P3*PrintSmall49less

P3*PrintSmall50to299
P3*PrintSmall300to499
P3*PrintSmall500to9999

I know how to use "IF" but then i can only use two choices, and not 4 like i want to.

=IF(P3>49;P3* PrintSmall50to299;P3*PrintSmall49less)

Thanks!

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

following is a solution to the problem ... i have generalized the problem a bit and I have also included an array based formula