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