yogi_Extract Unique Items From Column A That Are Not In Column B

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user AniaQue said:

How do I find text that is in Column A but not in Column B?
Hello everyone.
I'm trying to figure out how to get my spreadsheet to show only unique data in column A that is not in column B. Is there a way to do this with a formula?
Thanks :)
---------------------------------
following is one solution to the problem

yogi_Compute Allowance Earned By Kids For Chores Done

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com


user Robcsousa said:
Does anyone know how to give a character a number value? I'm a rookie Google spreadsheet user.
All I am trying to do is assign a value in $ to a cell that has an x or * or whatever in it. So for example, if my kid puts an x next to the chore on the spreadsheet, he get $ .50 in his bank.
a little technically challenged :-)
Basically, I'm experimenting with a chart for my kids that has chores/duties etc, and that's the way they'll earn an allowance. They would simply put an x next to the chore and it would calculate a dollar amount which would be totalled in another sheet, the bank. It's just my way of teaching them a lesson on earning and saving their money. I'm sure it's possible but I'm a rookie when it comes to spreadsheets. I really appreciate all the input you given.
Rob
--------------------------------
Goof Job Rob in Teaching Responsibility and Value of Money to Kids
following is one solution to the problem

yogi_Use SUMIF With Multiple Criteria

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user garfolino said
SUMIF with multiple criterias
A B
10 Market
15 Guest
32 Payment
41 Bar
20 Dog
43 Visitor
I wanna produce the SUM of everything that is Market, Bar and Dog, meaning that I want to exclude all the other options.
I've been using =SUMIF(B1:B6, "=*market*", A1:A6)+SUMIF(B1:B6, "=*Bar*", A1:A6)+SUMIF(B1:B6, "=*Dog*", A1:A6)
That is too long. So I was wondering if I could use something that says =SUMIF(B1:B6, "=*market*ORdogORbar", A1:A6) <- This is just an example, not a real formula... I did found an option that works for Excel I Created in another Column a RANGE with all the criteria, which is better because I can add or change criterias on the go, and not have to change all the formulas again. =SUMPRODUCT(SUMIF(B1:B6;"*"&$F$2:$F$8&"*";A1:A6)) So from F2 til F8 I have all the criteria including Dog, Market, Bar and others. But this doesnt translate to Google Docs. Any ideia?
-----------------------
in the following I have posted some alternate solutions to the problem



Update:
After I provided the first solution to the user, the user commented
This is where I found the SUMPRODUCT formula.
I just need to translate this into google docs...
http://www.mrexcel.com/forum/showthread.php?t=51693

So I added alternate solutions including the one using the SUMPRODUCT function

yogi_Use QUERY Function With Search Criteria Defined By Data Validation DropDown

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user kinngrimm said:
How do i get a value into a query defined by a dropdown(data validation) menu?
Firefox 7.01, Win7(64)
I have 2 dropdown menus(Data validation) which at some point should be used by another cell to get the values out of a table
1. dropdown menue
type: light,medium,heavy
2. dropdown menue
column names: foo,bar,foobar
The table looks like
type         foo          bar         foobar
light        40%          20%          10%
medium       50%          30%          40%
heavy        10%          70%          40%
The table is completly in a Range as Base for the Query
The Query should give me back f.e. '50%'=QUERY(Range,"foo' where 'type'='medium'")
1. Problem
Atm it is static in a cell but i dont get anything back, the cell i n which idefined thr query
looks like a function call
"foo"()2. Problem
Even if i would get back the value out of the matrix, i noticed that i cant put in the query dynamic changes so that when i change the dropdown menu, the outcome of the cell with the query would change
Any help and/or advice would be appreciated
-------------------------------------------
following is one solution to the problem

yogi_Rearrange Data By A Specific Attribute And Post In Another Sheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

user 'A Bunny Warrior' said:

Pull Multiple Info from cells to 1 column 

I have a spreed sheet with multiple sheets of information.  I am however only trying to use 2 of them for this.  One of them is for signups and the other is for those signups by class.  The people who sign up do so in a form.  Once they sign up there is a column named "Class" which could contain any of a bunch of different values.  (For example: Cleric or Wizard).  The second sheet is just empty, with the exception of the top row that contains all of the different class types.

What I want to happen is, whenever someone signs up, I want the second spread sheet to pull the information from the 'Class' column of the Signup sheet and place the person's name in a corresponding column in 'Sheet 2'.  So instead of having to jump back and forth from sheet to sheet, I can just see who I have and what class they are all lined up nicely.  
Any help would be greatly appreciated.
--------------------------------
Let me see if I have understood correctly in providing the following solution to the problem

yogi_Convert Multi Row Single Column Entries Into A Table Of Specified Number Of Columns

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user Keith Hinkle said:

Can I "transpose" rows into a table by making for example every 10 rows a single row of 10 columns?
for example take 600 rows by 1 column and convert it into at table with 60 rows and 10 columns?

a1

a2

a3

...

b1

b2

b3

......

a1 a2 a3 ...

b1 b2 b3 ...

...

-------------------------------
following is one solution to the problem ...

yogi_Use SUMPRODUCT On Data In Another Sheet And Its Equivalent For Data Filtered In The Same Sheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
ulviyelen said:
Hi ! I loved google doc and I wanted to use it for bill tracing for my work. I made a form that feeds a spreadsheets. Eveything is ok until I wanted to retrive some spesific data. Here is what I wanted to do:
On the different cell I want to have specific formula:
C D E
1) Amount Date Day(until pay)
2) 510 15-11-2011 30
3) 240 15-12-2011 60
.. ... ........ ...
for this two rows the formula is =ArrayFormula(SUMPRODUCT(C2:C3;E2:E3)/(SUBTOTAL(109;C2:C3))) > this works fine..
The problems are;
* I feed the sheet with a form. I mean adding always new data.
So if I write this to make it more expanded;
=ArrayFormula(SUMPRODUCT(C2:C10000;E2:E10000)/(SUBTOTAL(109;C2:C10000)))
subtotal works but sumproduct doesn't work. The empty cells also calculated and gives wrong results.
* If I filter results (for example only phone bills) the filtered result isn't correct.
I wish I hade sumproduct function_code for subtotal.
------------------------------
here are couple of solutions to the problem

yogi_Sort The Form Responses Data In Another Sheet By Most Recent Submittal First

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user jonnyuk10 said:
Can somebody please make a step by step guide on how to make the "newest" responses appear at the top of the spreadsheet
------------------------------------
I thought the best way to provide a solution to the problem is by creating an illustration ... so here we go
This is best done by creating a sister sheet in which the data in sheet Form Responses is soted in descending order by the TimeStamp column

yogi_Determine The Status Of An Entity From A DropDown List In Another Sheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

user TutorBrian said:
Very complicated IF then?

Pretty simple I imagine but I can't figure it out...
Sheet 1:  I have two columns.  One column is a list of all my students (400 students).  The next column is their status.  
Sheet 2:  I have two columns, one is a pull down menu so my teachers can select a student.  In the second column I want the cells to be automatically populated with the student's status from sheet 1 dependent on the student populated in Sheet 2 column 1.  
So, on Sheet 2 I want the second column to first look at the first column, see which student the teacher chose and populate it with the corresponding student status from sheet 1.
I'm not sure how to do this.
----------------------------------------
following are a few ways of solution to the problem ...

yogi_Create A DropDown List Of SubItems Based On A List Of Items In A DropDown List

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
Let us refer back to the solution for the problem posed by user Sathya Charana S.M about
http://yogi--anand-consulting.blogspot.com/2011/10/yogicreate-dependent-list-of-items.html
now the user want to create A DropDown List Of SubItems Based On A List Of Items In A DropDown List
-----------------------------------
following is my proposed solution to the problem

yogi_Create A Dependent List Of Items Based On A List Of Items In A DropDown List

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user Sathya Charana S.M. said:
If I compare with Microsoft Excel, we do NAME for the "Ranges Names" in Google Docs.
But when I am giving that particular "Range Names" at the time of using formula, I am getting error in formula.
Do you have any video to help to understand this issue or resolve this issue. I need to do a dropdown list which select from particular range of data as you have mentioned. But I am explaining it again below.
My selection would be like If I select "Fruit" from a dropdown list, it should show only fruits listed and if vegetables, it should show only vegetables and for Others, the others. May be my table of contents are like below.
Fruits
Orange
Grape
Apple
Mango
Vegetable
Onion
Tomato
Beans
Carrot
Others
Tamarind
Chilly
Salt
Pepper
Something like above will be my table.
please help me in this regard. You have already explained it. But I am unable to catch in that context. If you can give me editable file, I may understand, more. Because I need to see the Validation Window, what the options you have used. Even the Screenshots are ok for me.
-------------------------------
in the following is one solution to the problem

yogi_Consolidate InvoiceAmount AmountPaid And BalanceDue For Invoices

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
In response to a question from use DrivenRight
I provide the following solution to the problem
-----------------------------

yogi_Replace Affected Cells In One Column With Contents In A Column Of Another Sheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user StaffBar said:
Hope someone can help with an ArrayFormula problem...
I have a sheet called Sales which lists Sales Person Names and Sales Value as columns Person, Sales
There are multiple rows with names and values in.
Some of the names are incorrect in this sheet. I have another sheet with corrections in it.
The correction sheet contains Person, Correction as columns.
Is it possible to use an array formula in a third sheet to create a new version of the Sales sheet with the names corrected in it?
I need to be able to do this dynamically as the source system for the sales sheet can't be changed and the list of changes also alters frequently...
---------------------------
Following is one solution to the problem

yogi_Sort A Range By A Specified Column In Specified Order

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user alter73 said:
there is another question - is there a way that columns E and F are at once sorted by total time?. I want the best result (player and his total time) to be always the first one in a row 2. Of course I can use a list version of the spreadsheet and to manually sort the results by total time, but I want this sorted version to be permanent to publish it in this exact version. Is it possible?
---------------------------
following is one solution to the problem

yogi_Move Rows In Sheet1 With A Specified Value To Sheet2

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
jamietranscendgroup asked:
How do I create a function where eg. IF a cell in a worksheet 1 = 100% then the entire row is moved to worksheet 2 in the same workbook
------------------------------
following is one solution to the problem

yogi_Count Number Of Items In A List With Multiple Items In A Row Separated By A Specified Delimiter

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

yogi_Search For An Item In A Column And Return Information From Specified Fields

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user Tret said:
Name search that returns that rows information.
A Couple things:
I am trying to figure out how to add a search function to my spreadsheet. I want to be able to type someones name into a search box and have it return all the information on that person. For example I have hundreds of names in Column B, I want to search for a name and have it show not only the name from column B but the rest of the information about them from columns C,D,E, etc. Here is a sample spreadsheet of what the information looks like.
https://docs.google.com/spreadsheet/ccc?key=0Ao3KZ5KFVC26dHpxSkhhU2R3V2lydGNhX284WFdWZFE&hl=en_US
Also, is there any way to use the spreadsheet like a database and have a gadget populate the information searched. Similar to the Form that you can input information to but display it like a table. For example I search for someones name on the gadget and it references the spreadsheets information and then populates just that users results on the gadget/form. It would be really nice if this part was able to be published to a website like the input form can be.
Sorry if these questions were answered before, I tried searching for this but had no luck.
Update:
edit: I have tried the filter option, it kinda works. I get this error below when I try to filter an entire column. With my spreadsheet the amount of information is constantly growing and information grows past the filter and is then unsearchable. Also, that searching isn't what I'm looking for having to check and uncheck to see information is annoying.
Trying to apply filter to a region that conflicts with one or more tables. Please select a table or select a region that doesn't overlap any tables.
-----------------------------
In the following I am going to deal with Google spreadsheet solution:

yogi_Compute For Unique Names Totals By Specified Column

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user alter73 said:
I am working on spreadsheet which is a result of a publicated form, it looks simplified like this:
(A)PLAYER NAME (B)BEST TOTAL TIME (10+20)
(1) X 100
(2) Y 130
(3) Z 160
etc...
I can do a formula that filter only one distance and shows sorted list of players best times on this distance.
But I have no idea how to make a sorted list of players with total time (sum of results for distance 10 and 20). The best solution I know to have such a sorted list is "SORT" and "FILTER" formula but the biggest problem is how to also calculate this SUM of results in column C for every player.
This should be automatically working for larger data base with more players who provide their results using publicated form linked with this spreadsheet.
---------------------------------

yogi_Compute Instances Of Specified WeekDay Between Date1 And Date2

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user iantrapl said:
Trying to determine the number of Mondays within two dates, like number of Mondays with 03/01/11 and 06/01/11, 13. Should be able to use this formula for any day of the week within  two dates.
Hopefully this explains my goal.
-----------------------------------------

yogi_Create A Flag To Enforce Only Unique Entries In An Adjacent Column In A Field

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user zsimple21 said:
Google Spreadsheet and enforcing a unique value for fields, not allowing duplicate entries
I have created a google spreadsheet (acting as a database) that has numerous columns, name being of them, and I wanted to make sure the name field is always unique and no row can be created if the name field is the same as another row. Essentially creating a primary key for a database. Does anyone know how to do this in Google Spreadsheets?
If it helps, I created a form to go with the google spreadsheet(database) that will enter the data in to the sheet and would love to ensure a user does not enter the same name as someone else in the list already.
I would love to have a resolution to one of these problems to move on, please help if you can!
----------------------------------

yogi_Import Data That Itself Had been Imported To Verify That The Integrity Of The Imported Data Is Maintained

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
iantrapl said:
Can import data from the document "A" ....=IMPORTRANGE("0Au0oXAiis3xidEQ4akxUdHJMQk1RdmxlSW9XTExRZ2c","sheet1!b29") to document "B" but when I try to use the importrange function to get the data from "B" to "A" then the process fails even though both documents have the same public authority. Retrieived the Key for "B" document and using the same info for the sheet and location.
-----------------------------------------------------
user can not import into Sheet1!B29:B40 of spreadsheet A from spreadsheet B which was used to import the same range of data from spreadsheet A ... I know it sounds confusing but it is akin to circular referencing.
What I present in the following is to show that one can use ImportRange to import a range Sheet1!B29:B40 from spreadsheet A into cell B28 of spreadsheet B as the user had done -- then I import the data in cells Sheet1!B28:B39 of spreadsheet B (which was imported from cells Sheet1!B29:B40 of spreadsheet A) into Sheet1!A1:A12 of spreadsheet C that the integrity of the data has been maintained.

yogi_Match Cell In Specified Data Sheets And Display Name And Row Of Matching Data Sheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user zeddoc said:
I have a column of id-numbers in COLUMN A
In COLUMN B I want to show the SHEET NAME and ROW NUMBER where there is a match elsewhere in the spreadsheet. (Not sure what to do where there is more than one match, but I was assuming only one match for now.)
(I also would like to know for future use, if this can be done to search/match cells outside of the current google spreadsheet.)
---------------------------------------
In the following I propose a solution where the data sheets are in the same spreadsheet and there is only one match in the data sheets.

yogi_Concatenate Data From A Range Of Columns Separated By Specified Delimiter Ignoring Blanks

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user Guy said:
Hello Group, I am working on data where I need to combine around 200 columns and put the data in 1 column for around 10,000 rows. I have tried concatenate.
The problem is if i use comma (,) between columns there will be too many commas in the New column I have created to display the data collectively.
If i dont use the comma's my data is displayed but you cannot differentiate for example 1, 0.65, 7 from 10.657 which is how it appears at the moment.
If any one understands this, I need advice on the best way to proceed
Thanks
reference:
http://www.linkedin.com/groupAnswers?viewQuestionAndAnswers=&discussionID=72780639&gid=3124035&trk=eml-anet_dig-b_nd-pst_ttle-cn&ut=1c-WPsJkBXrkY1
----------------------------------------
In the following proposed solution presented here I have concatenated data in columns C:IV and I have used ArrayFormula and RegExReplace functions that are available in Google Docs spreadsheet ... in EXCEL one can enter the ArrayFormula using CtrlShiftEnter rather than just Enter, and the RegExReplace may have to be deployed as a custom written function (see http://lispy.wordpress.com/2008/10/17/using-regex-functions-in-excel/)

yogi_Compute Net Income From Daily Expense And Income Log

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
rogerisI8said:
I want to create a expense form/spreadsheet that will increment somehow to show each days expenses/incomes
I didnt think this would be that tough but it is turning out to be quite difficult. I wanted a form that could track my daily and weekly income/expenses.
https://docs.google.com/spreadsheet/viewform?hl=en_US&formkey=dDVZVUczak1rYTk4aXFZdXZRZXdNZFE6MQ#gid=0
https://docs.google.com/spreadsheet/ccc?key=0AlvQDPCualUtdDVZVUczak1rYTk4aXFZdXZRZXdNZFE&hl=en_US#gid=0
Thanks in advance for any help you might give on this problem or any you foresee trying to make this work
------------------------------------------------

yogi_ThanksGiving

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

yogi_Sum A Given Column For Specified Conditions

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
Katrine said:
Hi, I too have a question in this category, i think... :-)
I want to sum specific rows by the names like the following:
I Monday I 1000 I
I Tuesday I 1100 I
I Monday I 1200 I
I Tuesday I 1400 I
I Monday I 1300 I
I Tuesday I 1000 I
How do i sum all "Monday"-rows and all "Tuesday"-rows without having to mark them manually? Can i create somekind of rule so i just need to say =sum(monday) for instance?
----------------------------------------

yogi_Formulate String To Contain Only One Instance Of Specified Replicated Content

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

yogi_Compute Sum Of Specified Entities Over Specific Period Of Time

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
herkalaska said:
SUM from a FILTER in a range from NOW and the previous 30 days
I am building a Pilot logbook in a Google spreadsheet. I would like to SUM flight hours from each aircraft in a current date range.
Sheet 1
column A column B column C
date aircraft hours
9/1/2011 C-172 3.5
9/2/2011 C-206 4.5
10/1/2011 C-172 1.2
10/2/2011 C-206 5.5
I want to get all the hours from the C-172 for the past 30 days in a cell on a seperate sheet
IE; If todays date was 10/15/2011 the cell would = 1.2
I would also like the same info for the past 6 months. Then the above example would = 4.7
The product would look like this;
Sheet 2
column A column B column C
aircraft past 30 days past 6 months
C-172        1.2          4.7
C-206        5.5         10.0
I have tried a few formulus from other help posts but I havnt found any that combine all three cells
------------------------------------------------

yogi_Compute An Entry Based On Average Of The Preceding Unless Otherwise Specified

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
rja9003 said:
an entered number vs calculated number
I am creating a mileage log consisting of Date, Number of Trips, and miles driven. For dates that I have the data I am entering the data. For dates that I only have number of trips I want the spread sheet to supply a number that is the average miles per trip for data that I have.
Example:
Day Trips Miles
Sun   5    15
Mon   4    12
Tue   8
Wed   1    3
Thu   6
I need to avoid the circular reference so I was thinking something along the lines of IsNumber() but it does not work as I need it to. IsFormula() does not exist that I can find. How can I calculate Miles per trip using only the entered data and not value that has been calculated.
--------------------------------------------

yogi_Extract Email Address From A Text String And Remove Any Extraneous Characters

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

In the proposed solution here, among other functions, I used the RegExReplace function to remove any extraneous characters not allowed in email address
-----------------------------------------------

yogi_Form For Cheerful Giving Of Gifts For Children In Need

Please view the spreadsheet above for open slots ... and then if you are interested in committing to give a gift to a child in need, fill in the FORM below 

and SUBMIT. Your Form submittal will update the spreadsheet as presented in this post (you will have to refresh /reload the page on the browser to see the update in the spreadsheet.

The following is a graphic representation of what gifts have already been commited to by other thoughtful Gift Givers

yogi_Find Number Of Items Delimited By A Specified Character

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
a user asked:
How do I find the total number of instances of a character over a range of string cells?
I have a column in which the cells are either empty or contain a string. If it contains a string, it may either have a single item, or multiple items separated by commas. I need to know how many items total are in the column.
Example:
|A, B|
| |
| |
|A|
|B, W, S|
|G|
Should return 7.
I've figured out I can add the number of cells which are not empty(4) to the number of commas across the entire column(3) to get the answer: 4+3=7. Finding the number of cells which are not empty was easy, but I cannot find a way to count the number of commas. The fact that a cell may contain more than one comma is screwing up a previous suggestion I found on these forums. Any ideas?
-------------------------------------
I don't know about he example provided by the user, but I am going to use my hunch that the list of items is separated by the delimiting character