Cloud Computing -- Google Docs Way

Thursday, January 2, 2014

yogi_Merge Data From Several Sheets And Use QUERY Function To Extract Records Where First Column Is Not Blank

Google Spreadsheet Post #1466
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Jan-02-2014
post by ET153 (http://productforums.google.com/forum/?zx=ftkkyxcf4zk8#!category-topic/docs/spreadsheets/rTLA9fnYG9U)
VMerge, Query, and joining multiple sheets within one workbook

I have been working on this for a few hours now and can't seem to get it to work properly... Here is what I'm trying to do.

I have a workbook with multiple Sheets (currently 4 Sheets and one Master).

This is for tracking individuals and I'd like for each employee to update their own (I've already addressed permissions) and the boss would be able to view the 'Master' Sheet which would allow him to get his statistics and view employee progress. There is the potential for hundreds of names but likely less than 100 per Sheet during typical work.

Link to simulated (actually a copy) of document:

https://docs.google.com/spreadsheet/ccc?key=0AgJlC_flczXGdFBBVllVazBOSjh5ajZtcVFmZFNnMXc&usp=sharing

This is what I have in the Master Sheet A2 (in an effort to pull the information to this sheet)

=Vmerge(QUERY('EThompson'!A2:G); QUERY('Employee2'!A2:G); )

I tried adding the other sheets to Master and it works but I get about 100 blank spaces per sheet added to the master sheet. I tried the 'select * where Col1 is not null' after the final Query but I get an error through Vmerge ('All data ranges must be of equal width!'). I have tried several recommendations from other posts using importrange, having Query before VMerge and I can't seem to get the data to the Master sheet without the blank cells. My goal is to have each name and associated data merged to the Master sheet, without blank cells, and to be able to use filter so the boss can see each school, grade, alphabetical, etc and get the statistics he needs.

Clear as mud? Thanks for the help, I'm stuck.

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

yogi_Extract A List Of Names That Exist In Column A But Not In Column B (contaminated)

Google Spreadsheet Post #1465
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Jan-02-2014
post by Patric C(http://productforums.google.com/forum/?zx=ftkkyxcf4zk8#!category-topic/docs/spreadsheets/axej9go3HU0)
trying to use google spreadsheet to find names that appear in column A but not B.

I have a list of names in column A and a list of names in column B. Some of the names in A are the same as B. I am hoping to use google spreadsheet to find the names that appear in column A, but not column B.

Thanks,

Patrick

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

please note that when the following formula

=filter(A2:A; isna(match(A2:A; B2:B; 0)))

that I have used in a solution posted in another thread did not work

further investigation indicated that names in your column B list have some extraneous (other than space) characters

yogi_Compute Row By Row Minimum Score From Entries In B2 to H If Count In Rows Of B to H = 7

Google Spreadsheet Post #1464
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Jan-02-2014
post by QuadT(http://productforums.google.com/forum/?zx=ehy6y8chn284#!mydiscussions/docs/83KuHWqzeAQ)
ArrayFormula not continueing - AKA Using the MIN FUNCTION with ARRAYFORMULA
The function I'm trying to perform in an arrayformula is:

=ArrayFormula(IF(COUNT(B5:H5)=7,ROUND(MIN(B5:H5),2),""))

I've seen some alternative solutions instead of using MAX posted by Adam & Yogi with explanations that MAX doesn't work so well in ArrayFormula; so I assume since MIN & MAX are best friends that MIN doesn't get along well with ARRAYFORMULA either.

Although just having the correct formula would be AWESOME. It sure would be nice to have the sections of the formula explained so that I can figure it out on my own for future needs.

Thanks Everyone...I sure do appreciate this forum, I use it regularly!

---

Here's the sample spreadsheet

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

Wednesday, January 1, 2014

yogi_Sum Up Entries In Column B For Entries In Column A That Match List of Entries In Range C2 to C

Google Spreadsheet Post #1463
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Jan-01-2014
post by CoryT87(http://productforums.google.com/forum/?zx=f1190urkz7zs#!category-topic/docs/spreadsheets/-UrLFYhgeVM)
'=sumif
how would i use the =sumif formula with 2 words in it instead of just one?
---
here's an example of what im wanting to do:

Column A Column B Column C

aa 2 aa

aa 5 ad

ab 14

ad 0

ab 4

ab 19

ab 25

i want to do a =sumif formula like this: =sumif(A:A,"",B:B) where it adds the sum of both "aa" and "ab" because "aa" & "ab" both are the same type and the formula is inputted into column c. if that doesnt make any sense at all please let me know.

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

based on my understanding that you want to sum up amounts in column B for entries in column A that match items listed in C2:C ...

I present the solution to a bit more generalized problem as follows

yogi_MultiConditional Sum Of Amounts Based On Specified Month For Dates And Specified Item

Google Spreadsheet Post #1462
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Jan-01-2014
post by garfolino question by NWIJN (http://productforums.google.com/forum/#!topic/docs/AJWjFcIqyrY/discussion)
Thank you for your answers on this and other Vlookup questions.

I had a question.

Example:

Bar 11/22/13 $75

Bar 11/30/13 $75

Bar 12/30/13 $75

Food 11/30/13 $100

etc. I'd like to create a formula that sums if = BAR but only for entries in month of November. Is this possible?

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

yogi_MultiConditional Sum Using SUMIFS Function In Google New Sheets

Google Spreadsheet Post #1461
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Jan-01-2014
question by Mark Tice (http://productforums.google.com/forum/?zx=f1190urkz7zs#!category-topic/docs/spreadsheets/wmC9Z4l0-7s)
How can I sum values in one column (column D) based on a date range criteria in another column (A)?
How can I sum values in one column (column D) based on a date range criteria in another column (A)?

In Microsoft Excel with the sumifs function the initial formula looks like this: =SUMIFS(D:D,A:A,">8/25/2013",A:A,"<9/1/2013")

Once I figure that out I need to figure out how I can apply it to one page at the end of a 30 page workbook to count the same columns based on the same date range in every sheet in the workbook.

Any ideas or advice would be greatly appreciated!

Thanks,

Mark

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

I didn't see any problem with the formula you have delineated ... in any event have a look at the illustrative solution I have presented using various alternate formulations

yogi_SetUp Alternate Formulas For Highlighting Duplicate Entries In A Column For Both Case Sensitivity And Case Insensitivity

Google Spreadsheet Post #1460
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Jan-01-2014
question by Darwin Franceshi (http://productforums.google.com/forum/?zx=yqundymc57ay#!mydiscussions/docs/x8-Jn_ZXfsY)
How do make sure there are no duplicate entries in Column A?