yogi_Check For Partial Match In One Cell And Anothe Cell To Be Non-Blank

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

user tobiasgMC said: 
Partial Match in IF Statement I am trying to create a formula that will look for any instance of AB or LOA in one cell and if one of those is present then will check a second cell for "X" or notnull would be fine and finally spit out a 1 if everything is true. 
I could actually do this the other way around, look for X first and then match. . . The part I seem to have the most trouble with - partial match - I can get it to work in a =COUNTIF(a2:a20, "AB*") but I cannot get that to work in an IF statement. Some cells contain AB2 or LOA(germany) etc. 
Any advice would be appreciated. Using Chrome 17 on Windows 7 
Thanks, 
Tobias
------
Here is a sample spreadsheet
https://docs.google.com/spreadsheet/ccc?key=0AqV0yawICBOrdHhidGF0U0Z1cHNoQzJjTnZidVpGRVE 
I need a formula in J4 that checks to see if LOA,AB, or is blank in column C. Then checks Deposit column for X, then returns LX(if LOA and X), LA(if AB and X), P(if blank and X) finally if both columns are blank a "?" would be nice. LOA AND AB matches must be partial/fuzzy as other info is often in those cells. Any help you could provide would be wonderful. My end goal is to then count each of these at the bottom of column J. 
Thank you, 
T 
--------------------------------------------
 following is a solution to the problem
 

yogi_Print A Live Form With Its Fancy Fonts And Theme Image Applied To The Form

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

user msheffield said: How can I print a survey from live form? I'd like to have a physical version of my 'live form' survey, but it prints really awkwardly, without the fancy heading. Is there any way to print this in its fancy form? 
Thanks!! 
-------------------------------------------
You can not directly print the Live Form
1) with its fancy font(s) used in creating the FORM
    that the user sees  
    nor can you directly print the Live Form
2) with the theme image that you may used in the FORM
    that the user sees
a workaround to print the FORM in WYSIWYG (what you see is what you get) is to save an image of the FORM, then load it in a graphics program, and print it from there as described in the following solution to the problem

To view how the FORM with its fancy fonts and the theme image applied to the FORM looks like ...

from within the spreadsheet ...

• click on Form
• click on Embed form in a web page
• then look at following dialogBox that pops up, and
• click on the hyperlink where it says ... you can view the published form here

and up pops the beautiful Form with its fancy fonts and the theme image that was applied to the Form

now we want to print this beautiful form ... right? ... so let us press Ctrl P to print the form ... in the following image I show the print dialog box with an image of the form that would be printed ... oh oh no fancy fonts no theme image that was applied to the Form

and in the following I present the  image of the form that got printed after pressing Ctrl P or if you got to the print dialog box some other way

So, what do we need to do to print our form with its fancy fonts and the theme image is ...

• to copy the image (or take a screen shot) of the portion of the form that we want to print or save it as a graphic file, then
• go to our graphics program such as irfanView or some other favorite program of yours, and paste the copied image or open the saved file, and then
• print from there

In the following I present an image of the form that I printed with its fancy fonts and the theme image that was applied to the form

yogi_Insert Current Date At Different Times And Hold (Meaning Entry Remains Static)

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

user Luiz Felipe said: 
This timestamp Function is updating Hi, I just added the timestamp funciton, and used it as some users dicribed here, but the date and time are beeing updated, sometimes when I open the spreadsheet. Could anyone help me ? i just need to put the date(can be with or without the time) at the first column(A) every time that column(B) get a value. 
Tks, 
Luiz 
-----------------------------------------------
following is a solution to the problem using DataValidation In this workaround I use Data Validation from a List ... the List consists of one cell with the following formula ... =today()
 

yogi_Extract Unique (Non-Duplicated) Entries From An Address List

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

user ibennani said: 
Remove duplicate entries from a address list 
(I've been searching a lot without any success)
Hello. I have a huge list of addresses and phone numbers to a lot of people. Some people live together and therefore they also have the same phone number. Therefore I would like to check if two or more persons have the same phone number. If so, I need to hide the duplicates. If possible, I would only show the first entry with that phone number. 
Any ideas of how to solve this issue?
-------------------------------------------
in the following solution to the problem, I have extracted non-duplicated entries
 

yogi_Compute Personnel Turnover Stats By Site Date And Quarter

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

user Somewhat... said: 
Personnel turnover tracking spreadsheet problems I am attempting to create a spreadsheet that will tabulate data for based on multiple conditions. I'm not certain if this is possible the way that I want it, but figured this would be a good place to ask. I've looked over other people's questions and haven't found anything so similar, that it helps my cause. 
I've created a sample of what I'm trying to accomplish here: https://docs.google.com/spreadsheet/ccc?key=0As849t1zRsg1dDFrMHRpNmlSdFloZWJXV2hIVi1Obnc Basically I would like to be able to add personnel in any order to the "Input Data" tab as people get hired and let go. I would like the data to formulate on the corresponding pages after it based on if they are in the US or EU sites. 
Basically I'd like to be able to track how many people work at each site (at any given time and monthly) and how many have been hired or have left (monthly and total). If this could tabulate automatically when I add the dates, all the better. This seems doable, but I keep getting stuck. 
Any help would be greatly appreciated!!! 
-------------------------------------------------
following is a solution to the problem
 

yogi_For A Given Birthday Compute Age In Years As Of This Day (Current Day's Date)

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


use lopis75 said: 
age (year) calculation 
Hi 
I have a BIRTHDAY DATE in cell A1 and I need the FORMULA to put in cell B1 to get here the AGE of the PERSON UPDATED EVERY SINGLE DAY (the age must be in Years!!!, no months and days, just in YEARS!!!) who can help me? 
Thanks LUCA 
-----------------------------------------
following is a solution to the problem
 

yogi_Tabulate Name And Number Of Times A Name Has Been Entered In A Column

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

user orlando said:
Count repetitions
I have a spreadsheet which displays a series of names (James, John, George, etc.), and I need to know how many times are they appearing (repetitions) on the colum.
How can I do a check on a colum and present on a separate cell information on how many times is each name repeated (appears), such as:
Example->
James:  3
John:   4
George: 2
etc.
thanks
 ---------------------------------------------
following is a solution to the problem
 

yogi_Sort Entries From Form Submittals And Manual entries

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

user jebmusic said: 
How to sort spreadsheet form entries AND manual entries 
 Hello, 
 thanks in advance for you responses. I use forms to collect, sort and filter data. Sometimes I like to just add the data manually though. When the data is added through a form, it becomes highlighted in grey on the spreadsheet. when it's entered manually, it isn't. Thing is, when I sort, it only sort the entries from the form. I want to be able to enter it both ways and sort them as one. Thanks!
----- 
When you enter stuff through a form, the background is grey. What does this indicate? Is there a way to change the formatting on the manually entered cells to have the same properties? BTW just to be clear, I am using the functions: SORT and FILTER. 
Thanks Jason 
-------------------------------------------
When you enter data via a Form the data is logged in a Table in a so called Form Responses sheet. The Table has a gray background and the Table has a whole can be sorted by itself -- if you make manual entries in columns to the left or the right of the Table, you can not sort those together with the Table in the Form Responses sheet. You can however sort the Table along with the manual entries in columns to the left or the right of the Table in a sister (another) sheet using functions such as SORT, and QUERY as shown in the following solution to the problem

yogi_Highlight Cells That Are The Same

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

user Kakarat said: 
How do I highlight cells that are the same 
Hi all. 
How do I highlight cells that have the same value by making use of conditional formatting. I have numbers in B3:G3 which I am comparing to B4:G4. When both of the ranges have for instance the number 6 in them, I would like the cells to be conditionally formatted. 
Thx 
----------------------------------------------
One can conditionally format a cell in Google spreadsheet for a specified value -- as of this time (anyway) Google spreadsheet does not allow conditional formatting by comparison to value in another cell.
In the following workaround approach I have generalized the solution by creating an intermediary row between the OP specified ranges of B3:G3 and B4:G4 ... so the OP's data is in cells B3:G3 and B5:G5, and I use the cells B4:G4 for comparing values in cells B3:G3 to those in B5:G5 and conditionally format the matching cells in B4:G4
 

yogi_Match Specified Attribute In An Array And Concatenate All Associated Email Addresses

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

My following solution to the problem came about from a discussion with my dear friend and fellow Top Contributor on Google Docs Help forum TedJ -- a simple row by row formula sported a rather straightforward solution, but the idea was to come up with an arrayformula that will lend itself for auto propagation in use with Form input.
 ----------------------------------------------------
so here we go ... I realize it is a pretty convoluted solution ... and if I come up with a simpler and/or more streamlined solution, i will post an update ...
 

yogi_Count Number Of Sales Made In A Specified Month by Specified Source

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

user Brennan Morrow said: 
I spent another four hours trying to figure out one cell and I think I'm stumped. I just don't understand the spreadsheet language enough to know the right statement for the equation. on the sheet Yogi_Jan1 Cell D3 I am trying to count how many sales were made in the month. In sheet finished ROI marketing all it is figured out for all the data (cell D2). In the Yogi sheet I am trying to filter those results by the date. I think it is the same equation that you help me with earlier, with a parameter for "ifnumber". I know you have helped so much, I hate to ask for more. I'm just truly stumped, tried everything I could think of, and can't get it give my results that I'm looking for. 
----------------------------------------------------
following is a solution to the problem
 

yogi_Compute Sum Of An Entity By Days Weeks Months And Chart Trend As TimeLine

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

user digitaltoast said: 
Normalizing and grouping dates and ranges of days into weeks and calendar months 
I have a spreadsheet pulling together various readings taken of hours heater has run, electricity units use for that period, and outside temperature. For the last 3 months, the electricity usage and heater hours have been taken every Monday. For 5 months before that, it was taken randomly between 5 days and a nearly a month in one case. The external average temperature is taken daily and added to the spreadsheet. I'd now like to display this data as broken down into 7 day weeks, and summarised by month. Having just about wrapped my head around the differences between Google Charts, Gadgets and Visualization APIs, I know that the display of it can be done. Where I'm struggling is grouping the daily temperature readings into weekly averages, the old "non-weekly" readings into weeks, and the fact that a Monday isn't always the start of the month and a month isn't always x amount of days. Thankfully, Google Docs makes the difference between dates as easy as A2-A1. And there's the WEEKDAY(number, type) which returns the day of the week for the given number (date value). 
Date #days kWh Reading Day 
15/04/2011 1 6 Tue 
10/05/2011 25 152 Fri 
15/05/2011 5 28 Sun 
I know how many days there are between 15/04 and 10/05 (UK dd/mm/yyyy date format!) and I know how much that makes the average to be; what I'd like to end up with is: 
Date #days kWh Reading Day 
18/04/2011 1 6 Mon 
18/04/2011 7 6.08 Mon 
02/05/2011 7 6.08 Mon 
09/04/2011 7 6.08 Mon 
And this is where I'm failing. I can round to the nearest week, I can average 7 days of temperature to a week, but turning random ranges into neat weeks and week into neat calendar months is melting my brain. I'm happy to sacrifice a few days data in 04/11 to "normalize" it to a new week. I realise that the "granularity" of the few fews readings won't be as good as the new weekly readings. Given that this is a "one-off" correction, if it needs to be done outside Spreadsheets, either externally or tediously manually, then so be it. But I feel there must be a function that can support this. Thanks - first "webapps" post so please be gentle :) 
---------------------------------------------------
I am not quite sure about the data that digitaltoast has presented ...
so I have made some assumptions about the data ... but that should not affect the approach for solution to the problem ... so here we go
 

yogi_Parse Field Text To Remove ?()' And Reformat To All Lowercase With Dashes Between Words

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

user sofa555 said: 
Function to parse(?) and reformat field text to all lowercase w/ dashes between words 
Hi everyone, 
I was hoping someone could help me find a solution for this seemingly simple problem. I am trying to take article titles from a column and format them as they appear in my website's URL. So for instance, "How do I do this?" actually becomes "how-do-i-do-this". So the question mark is removed, all the text is converted to lowercase, and a dash is inserted between each word. Apostrophes and parenthesis would also need to be stripped. So "What's the best way to say 'I love you'?" would become "what-s-the-best-way-to-say-i-​love-you". By combining that field with a category field and the full domain, I want to create a clickable URL that appears in a spreadsheet. Is there a function to accomplish this? 
Thanks in advance to anyone who can assist.
-----------------------------------------------
following is a solution to the problem
 

yogi_Search For Entities Within Strings And Extract Associated Cell References

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

user mr_white said: 
separate values in a single cell? 
How can I put two separate values into a single cell so that I can filter by either. Specifically, 
C1 Paramount, warner brothers 
D1 Warner brothers 
E1 paramount 
F1 miramax 
G1 Paramount 
So, if I choose it filter by Paramount. It would show, C1, E1 and G1. By Warner Brothers, C1 and D1. The problem I am coming across is it sees "Paramount, Warner Brothers"as a single value. I would like them to be seen as separate ones. Thanks! 
------------------------------------------------
I had a little play with it
following is a solution to the problem based on what I would like to do
however I have a hunch that this may not be what mr_white is after ... in any event here we go
 

yogi_Filter Numbers In A Column Which Contains Both Text And Numbers

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

user imonion said: 
FILTERING NUMBERS IN A COLUMN WHICH CONTAINS BOTH TEXT AND NUMBERS Hello, 
 I have a spreadsheet with a column that contains data like this:  
A1 MERINO SOCK 
A2 0 
A3 MERINO/NYLON SOCK 
A4 0 
A5 BFL SOCK 
A6 0 
A7 MCN SOCK 
A8 2 
A9 SPARKLE SOCK 
A10 0 
A11 GAIA SPORT 
A12 0 
A13 GAIA WORSTED 
A14 5 
A1 is a product, A2 is the quantity of that product that has been ordered. I would like to filter out the products that have a quantity of zero. I am trying to create a packing list of sorts that only lists products with quantities >0. 
Thanks. 
---------------------------------------------
 Following is a solution to the problem
 

yogi_Set Up The Spreadsheet To Score An On-Line Quiz And Return The Result To The Quiz Taker

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

The Form Responses sheet presented here is populated by filling a Form for an On-Line Quiz at:
http://yogi--anand-consulting.blogspot.com/2012/03/yogiscore-on-line-quiz-and-return.html 


So, here I show the computed cells setup in the Form Responses sheet which display the score of the Quiz taken at the link referenced in the preceding para.

yogi_Score An On-Line Quiz And Return The Result To The Quiz Taker

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

Take the Quiz by clicking on the associated hyperlink
 or
 fill-in the following Form:

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

After You Submit The Form
click the GoBack button on your browser, then
Your Score with the date and time you took the QUIZ on and your ID will be displayed within a few minutes on refreshing your browser

 

 

yogi_Merge Data In A Column Including Blanks From Different Sheets By Appending Consecutively

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

In an earlier post ...
http://yogi--anand-consulting.blogspot.com/2012/02/yogimerge-data-in-column-from-different.html
I had offered a solution yogi_Merge Data In A Column From Different Sheets By Appending Consecutively  there were no blanks in the data sets to be merged from several sheets.
--------------------------------------------------------
In the following solution the data sets in different sheets may contain blanks

yogi_Merge Data From Several Sheets And Extract Only Non-Blank Rows

Yogi Anand, D.Eng, P.E.                Google Spreadsheet          Post  #473         www.energyefficientbuild.com
user WEEN said:
Dear googlers duplicate a spreadsheet but formula can work ? I ve duplicate a complete sheet and i have rename just the title of the spreadsheet It appears that in one sheet i have the following formula that used to work in the original document but not in the copied one , how is it POSSIBLE ? =query(vmerge(SH1!A10:CH;SH2!A10:CH;SH3!A10:CH;'RERANGE SH4'!A10:CH),"select* where Col1<>'' or Col2<>'' or Col3<>'' or Col4<>'' or Col5<>'' or Col6<>'' or Col7<>'' or Col8<>'' or Col9<>'' or Col10<>'' or Col11<>'' or Col12<>'' or Col13<>'' or Col14<>'' or Col15<>'' or Col16<>'' or Col17<>'' or Col18<>'' or Col19<>'' or Col20<>'' or Col21<>'' or Col22<>'' or Col23<>'' or Col24<>'' or Col25<>'' or Col26<>'' or Col27<>'' or Col28<>'' or Col29<>'' or Col30<>'' or Col31<>'' or Col32<>'' or Col33<>'' or Col34<>'' or Col35<>'' or Col36<>'' or Col37<>'' or Col38<>'' or Col39<>'' or Col40<>'' or Col41<>'' or Col42<>'' or Col43<>'' or Col44<>'' or Col45<>'' or Col46<>'' or Col47<>'' or Col48<>'' or Col49<>'' or Col50<>'' or Col51<>'' or Col52<>'' or Col53<>'' or Col54<>'' or Col55<>'' or Col56<>'' or Col57<>'' or Col58<>'' or Col59<>'' or Col60<>'' or Col61<>'' or Col62<>'' or Col63<>'' or Col64<>'' or Col65<>'' or Col66<>'' or Col67<>'' or Col68<>'' or Col69<>'' or Col70<>'' or Col71<>'' or Col72<>'' or Col73<>'' or Col74<>'' or Col75<>'' or Col76<>'' or Col77<>'' or Col78<>'' or Col79<>'' or Col80<>'' or Col81<>'' or Col82<>'' or Col83<>'' or Col84<>'' or Col85<>'' or Col86<>'' ") please explain me why thi doesn't work, here here a copy of the page you can try to see my formula is in the test sheet https://docs.google.com/spreadsheet/ccc?key=0Atv06cGw9P0fdEc5cS14NmpPU1dZYm9oUjE3dHVrYWc 
regards
------------------------------------------------------------
WEEN's formula as shown in sheet Test seems to be OK ... but it is way too long and repeating with OR for 86 columns one at a time sequentially ... WOW!
In the following solution as presented in sheet Yogi_Test I took a little different approach and I came with a relatively much shorter formula ... so here we go
 

yogi_Compute Practice Time By The Day And The Week

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

user OmanGuitar said: What's formula will make this happen? I have been working on this for some time now and I'm slowly making progress, but I seem to be stuck. I'm trying to summarize data and give daily and weekly totals automatically. I have a filter that gets all new day time stamps a column for weekday (1-7) and a column to mark the beginning of new weeks. My goal is to get the "total hours" for each day and week. I was trying to use =dsum but that doesn't seem like it's going to work. Any other ideas? Here's the spreadsheet: https://docs.google.com/spreadsheet/ccc?key=0Airsv1lu5fdWdE15aVRZRU15eG55R2ljZC1PVENRQ2c Thanks!
-------------------------------------------------
following is a solution to the problem
 

yogi_Split Columns Of Assignments By Name And Day Into Columns Of Names By Assignment

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

user ppetak said: 
Hi all, 
i have a little problem - I have two columns, in one names of people, in other their assignment in one line with comma as delimiter. What I need is the other view on the problem - in first column all assignments, and in other all people names in one line. like: 
name| assignment_monday         | aasignment_tuesday | .... 
Joe | cooking, dishes, shopping | cooking, playing | ... 
Bob | cooking, playing, shopping| .... 
Me  | playing                   | .... 
and result: 
assign  | monday   | tuesday 
cooking | Joe, Bob | Joe ... 
dishes  | Joe 
shopping| Joe, Bob 
playing | Bob, Me 
I have found two solutions, one is using arrayformula: =arrayformula(CONCATENATE(IF(FIND(A23;B$2:B$6),CONCAT($A$2:$A$6;", "),""))) 
 which works as long as I have only one assignment in column, because FIND function as in my example not work on arrayformula. 
Or am I missing something? another way I almost succeeded is : 
 =join(", ";(query(A$2:B$12;"select A where C contains 'cooking'")) 
this is working well for Monday. But what about Tuesday? Forget it! Now the problem - it eats only one array range, but I have names in first column only, and I need to ask for each column (monday, tuesday, etc) separately. And I don't know how to join columns so it appears for the query as one block. I mean, I need to feed the query with something like this: A2:A16;C2:C16 where column A are names, C is assignment_tuesday. Anything I was able to find are some crazy constructions like (CONCAT(SPLIT(TRANSPOSE(SPLIT(CONCAT(....))))) but everything I want to do is put it along for query - there must be easy way, isn't there? I wonder why the most simple things NEVER work, spreadsheet has TONS of functions used one time a year, but this? I imagine devs talking over the coffee: "Too simple guys, lets make it really annoying, should we?" 
--------------------------------------------------
following is a solution to the problem
 

yogi_On-Line Calculator For Difference Between ToDate And StartDate In Years Months And Days

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

click on this hyperlink to Fill The Form and Submit FromDate and ToDate
or
Fill-in the following Form right here to Submit FromDate and ToDate

yogi_Find The Maximum Value In A Range And Detrmine The Value In Another Cell Per User Specification

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

user *KC said Finding the Max number and returning outcome from a different cell Hello Below you will see a sample of a spreadsheet I am working with. I am able to find the max number from the row of information, but the outcome I am seeking, is actually the "source" that is in the cell directly before the max number. How can I look up the max number and return the cell listed before it? So the outcome I would hope for in Row 4 would be.......... SA (source 2 from column E) this is because it was the max number of 13640. For Row 5 I am hoping the outcome will be PU (from source 4) Col. C D E F G H I J Source 1 Amt Source 2 Amt Source 3 Amt Source 4 Amt Max Row 4 KO 8431 SA 13640 PU 5462 OK 265 13640 Row 5 MTV 1000 SLP 2000 OK 500 PU 30000 30000
-----------------------------------------------------
 following is a solution to the problem as I understand it
 

yogi_Concatenate Non-Blank Cells In A Specified Range With A Specified Delimiter

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

user cowboy713 said: 
Hi everyone, my names Bill. I'm attempting to concatenate multiple large ranges of cells for a project I'm working on. I got my function down to =ArrayFormula(CONCATENATE(U4:W33&", ")) which is working great and pulling any data in those cells into a new one for me, however some of the cells included in that range are empty. Is there any way to change this function so that empty cells are not included in the concatenation? Right now my data's coming out as ",,,,,Value 1, Value 2,,,,,,,,,, Value3,,,Value 4,,, Value 5". Obviously I'd like to avoid this :-) Thanks! UPDATE user cowboy713 said: Nevermind, figured out a VB macro to include an if statement to check for length. If anyone's curious I added it below. Function ConcatenateRange(myRange, Separator) FirstCell = True myRangeValues = myRange.Value For Each thecell In myRangeValues If FirstCell Then ConcatenateRange = thecell Else If Len(thecell) > 0 Then ConcatenateRange = ConcatenateRange & Separator & thecell Else End If End If FirstCell = False Next End Function
-----------------------------------------
 Well, following is a formula based solution to the problem that can do what needs to be done without using a macro be it it a VBA macro as suggested by cowboy713 for Excel or a macro developed using Google Apps Script. So, here we go ...
 

yogi_Select Dates For Only Specified Weekdays From A Range of From And To Dates

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

user stroh.ed said: Workday Formula Shift I'm working on developing a scheduler for the company I work for. The section I'm working on has to do with turnaround dates for orders of different sizes. As of right now the formula for calculating the due date looks like the following: =WORKDAY(NOW(),D2+ROUNDUP((FlightPanel!H3))) (FlightPanel!H3 is a backlog modifier to adjust the date to be further out based on workload) The problem at the moment is that this method only gives me due dates for weekdays but the nature of the work permits due dates on Saturdays but not Sundays or Mondays. Is anyone aware of a way to make the formula permit only dates including and between Tuesday and Saturday? Any help would be greatly appreciated. 
-----------------------------------------------
following is a solution to the problem of selecting dates for only specified weekdays from a range of from and to dates
 

yogi_Add Values In Columns Based On Specified Pattern And Criterion

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

user BwC13 said: Want to add an array based on If function This is what I am doing. I have prioritized cells in column C with a corresponding 1,2,3,4,or 5 in column b. I want to add all the cells in column c that have a corresponding 1 in column b. I know I can do this: if(b2=1, c2)+if(b3=1, c3) But I have a ton of cells to add up so I hope someone knows a shortcut. In addition, I also want to include 3 more "add" columns and their corresponding "1,2,3,4,or 5" column to the equation. I can probably figure this part out on my own if someone can do the first part for me. thanks 
UPDATE: 
user BwC13 said:
Found a solution: =sumif(B2:B17,"1",C2:C17)+sumif(E2:E17,"1",F2:F17)+sumif(H2:H17,"1",I2:I17)+sumif(K2:K17,"1",L2:L17) Thanks for looking 
---------------------------------------------------
I noticed that there is a pattern to user BwC13's data ... so if there would be many more sets of columns to be considered and perhaps some other pattern, it would be nice to have a generalized solution ... and it is for that problem that I have presented a solution in the following:
 

yogi_Use VLOOKUP And HLOOKUP In The Same Formula

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

user PriestessMars said:
Can you use VLOOKUP and HLOOKUP in the same formula?
Is it possible to use VLOOKUP and HLOOKUP in the same formula? For example:
Sheet 1
A B C D
1 Name   Address          City         State
2 Sara   123 Main Street  New York     NY
3 Tom    345 Apple Place  Los Angeles  CA
4 George 567 Maple Lane   Boston       MA
4 Abby   789 Rigel Ave    New York     NY
Sheet 2
A B
1 Name State
2 Sara ???
3 George ???
I want B2 to come back with NY, B3 with MA. I know I can use the VLOOKUP function to get there, but if I have 20 different columns and only want the sheet to contain the information for some of it, is there a way I can have it lookup Sara & George where the column header is "State" (or some other text value)?
Thanks for the help and I'm sorry if this question has already been asked.
---------------------------------------------------
following is a solution to the problem

yogi_Set Up Formula To Tabulate Number Of Correct Responses

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

user brainbrst said:
Arrayformula to tabulate number of correct reponses
I have a spreadsheet fed by a form with 20 multiple choice answers. In the last column of the row of each form submission I would like to tabulate the number of correct answers in relation to a second sheet from the same document. Is this possible to do with one formula or do I have to populate another sheet with a tally of each individual correct response and then count those?
so for example
response sheet Correct answer sheet (just 1 row or column with all correct answers. I would prefer column because there are other fields I'd like to include with the answers)
resp1| respr2| resp3|....|total correct aswr1 | aswr2| aswr3
  A  |   B   |   C  |       1 ........... A       C      B
. B  |   B   |   B  |       1
  A  |   B   |   B  |       2

---------------------------------------------
following is a solution to the problem

yogi_Set Up Leader Board For Top Scores -- MarinusP or AdamPL

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

In the following thread ...
http://yogi--anand-consulting.blogspot.com/2012/03/yogisum-up-amount-spent-by-category.html
there ensued an interesting discussion on the Leader Board for Top Scores -- the original poster Rhyno Max can settle the issue ... in the mean time we have two Leader Board scenarios:
1) MarinusP Leader Board
2) AdamPL Leader Board
the issue was which is the one to use. I thought why not set up Two Leader Boards, one proposed by MarinusP, and the Other by AdamPL
------------------------------------------
so without much further ado here we go:

yogi_Compare 2 Columns Of Names And Remove Names Listed In Column 2 From Column 1

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

user Stoked Charlie said:
Compare 2 columns of names and remove names listed in column 2 from column 1
I have a mailing list of e-mail addresses (column A) and another list of e-mail addresses (Column D) that need to be removed from the mailing list.
How can I have google docs, compare the lists and delete the matches?
If it is not possible to delete the matches, can google output a list of matches into another column, in order for me to delete them from the mailing list.
Any and all help is appreciated.
Thanks.
-----------------------------------------
following is a solution to the problem

yogi_Sum Up Amount Spent by Category

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

user biggiesmalls said:
Not Sure what function to use or how to set it up
A B C D E F
Date      Place        Amount  Sales Tax   Category 
2/22/2012 CCHMC         $6.24              food     
2/22/2012 Costco gas   $65.00              gas 
2/25/2012 Once Upon a  $63.36       3.87   well being
2/25/2012 Serenity      51                 well being 
2/25/2012 mcd            6.72              food
I am looking to sum the dollar amounts in Column C based on whether it is labeled as food ,gas, grocery etc in column E (Category). I want to track my expenses based on what type of expenditure it is. The total is to be reflected in column G. I am guessing from what I am reading that I need to use the sumif function entered as: SUMIF(E4:E11,food,C4:C11). This says error not sure what I am doing wrong. Can someone please help? Thanks.
----------------------------------------------
if you want to sum up only 1 item such as food use
=SUMIF(E4:E11,"food",C4:C11)
to get all the category items summed by have a look at the following solution to the problem

yogi_Sum Up An Item In A Column To Fulfill Multiple Criteria

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

user sjchow said:
How to sum total amount in 1 column which needs to fullfill 2 criteria in separate column?
Hi,
I have been trying to computer the total annual leave consumed by each employee.
Column C= Employee code (example: jeremy lee = jjlee)
Column D = no of days employee applied for. (Number is either 0.5 or 1 represent half or full day)
Column E= Type of Leave (AL= Annual leave...etc)
For example, how do I calculate the total annual leave taken by Jeremy?
I have tried : "=SUM(SUMIF(C:C='sjchow',{0.5,1},E:E="al"))" but it still prompt parse error.
I have tried filter function as well. It did successfully but I can't sum up the whole column amount.
Thanks!
------------------------------------------
following is a solution to the problem

yogi_Compute Entity Per Specification In Column(s) Row By Row

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

user celsius said:
arrayformula correct syntax?
=ArrayFormula(if(len(I2:I);(QUOTIENT(J2:J,3)*25)+(MOD(I2:I,3)*10);iferror(1/0)))
1. Is this correct syntax? including parentheses (It is not working).
2.I believe this is checking to see if there is a valid number value in I2 (& then proceed), but I need to check for a valid number value in both
I2 and J2.
4. Also, where do I place my title for the column as in something like
IF(ROW(A:A)=1;ʺPriceʺ;
Thank you again.
UPDATE
Thank you Yogi!
Your formula worked
=ArrayFormula(if(row(YY:YY)=1,"Price",if(len(I:I);(QUOTIENT(I:I,3)*25)+(MOD(I:I,3)*10);iferror(1/0))))
How do I add in a summing of the same algorithm for column J (in addition to column I).
Maybe - can one sum formulas, or do we another + with brackets?
=ArrayFormula(if(row(YY:YY)=1,"Price",if(len(I:I);(sum(QUOTIENT(I:I,3)*25)+(MOD(I:I,3)*10):(QUOTIENT(J:J,3)*25)+(MOD(J:J,3)*10));iferror(1/0))))
---------------------------------------------
following is a solution to the problem:

yogi_Compute And Write Number Of Form Submittals At Change Of Date In A Computed Column

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

yogi_Use Split With An Array Correctly Handling Items In The First Row As Well

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

user istril said:
using split with an array not working as expected, also won't sort
here is mydata:
libj:_old_do_NOT_      0   0   0   0   0   0   0    0
Libb:_Crown_Point     82  82   0  76   7  82   6 1266
Libc:_East_Chicag      0   0   0   0   0   0   0    0
Libd:_Gary             0   0   0   0   0   0   0    0
Libe:_Hammond          2   2   0   1 100   2   0  100
Libf:_Jasper_Coun    138 138   0 141  -3 138  11 1154
Libh:_Lake_County     11  11   0  11   0  11   0  100
Libg:_LaCrosse         2   2   0   2   0   2   0  100
Libk:_Lowell         118 120  -2 122  -4 118  10 1080
Libi:_LaPorte          2   2   0   2   0   2   0  100
Libl:_Michigan_Ci      1   1   0   1   0   1   0  100
Libm:_North_Judso      1   1   0   1   0   1   0  100
Liba:_Any_Other_L     11  11   0  14 -22  11   1 1000
Libn:_Pulaski_Cou      0   0   0   0   0   0   0    0
Libo:_Starke_Coun      2   3 -34   4 -50   2   0  100
Libq:_Westchester      3   3   0   2  50   3   0  100
Libs:_Whiting          1   1   0   1   0   1   0  100
Libp:_Wanatah          0   0   0   0   0   0   0    0
Libr:_Westville        2   2   0   2   0   2   0  100
when I enter the data, and then use
=split(A1," :")
and drag the formula down it works fine. (I am using both a space and a colon as delimiters)
BUT, this data is actually buried in a lot of other data, so I am calling it with a filter. When I use:
=arrayformula(split(filter("mydata")," :"))
it behaves oddly: for the first row of data, it will populate the first cell (the cell that actually contains the formula, so in this case it has "libj") but no other cells in that row. The data that would normally appear in that row is just gone. There also aren't any of the "--" in that row that you normally see when an arrayformula is continued. The rest of the range seems to populate as expected.
One other odd bit of behavior is that if I try and wrap this in a "sort" function, it doesn't sort at all. I have tried wrapping different chunks of the formula in sort and I can't get it to work anywhere.
anyone know why this isn't working?
------------------------------------------
there is a problem with the Google spreadsheet's SPLIT function not correctly handling the data in the first row of an array (as of this time) is a known problem ... the problem has been intimated to Google Docs Team

so I use a workaround by introducing a blank row about the first row of the real array as shown in my following solution to the problem

yogi_Publish An Image Inserted In A Cell Of A Sheet

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

user BurnoutAddict said:
Image not publishing
I'm having a bit of an issue with an image not showing when publicly publishing a spreadsheet in Google Docs.
There is only 1 image in the whole of my spreadsheet document.
Heres the link to view the file as I see it (before publishing):
https://docs.google.com/spreadsheet/ccc?key=0Avs8djRUmQ2fdFFlVURlaW9oSEVFa0IzUDkwNnpEZ1E#gid=6
The image is on the "Scores and Information" sheet.
Heres the document after publishing:
https://docs.google.com/spreadsheet/pub?key=0Avs8djRUmQ2fdFFlVURlaW9oSEVFa0IzUDkwNnpEZ1E&single=true&gid=6&output=html
Note that only the Scores and Information sheet is published. I still get the same result if the whole document is published and viewable.
The image is from my Picasa Web Albums and is viewable to anyone with the link.
Anyone got any ideas as to why this is happening?
--------------------------------------------------
I tested publishing the image by itself ... and as you can see it did get published