yogi_Rearrange MultiColumn Entries For Asociated Entities According To DataBase (List) Rules

                                         Google Spreadsheet   Post  #1517
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-05-2014
post by Tidoko and question by Daniel  (https://productforums.google.com/forum/#!msg/docs/mLcdO0c2QEc/F7yNO9wdhVsJ)

Hi --Hyde,

Thank you for sharing your knowledge!

Hi have a table like this, but with lots of data !

			ABKI 1000	ABKI 2000	ABKI 3000
John	02/04/2014 18:46:00	02/04/2014 18:46:00	140	170	200
Mark	02/04/2014 18:46:00	02/04/2014 18:47:00	200	230	250
Peter	02/04/2014 18:47:00	02/04/2014 18:47:00	40	50	60

and I need to convert the data in red to single rows combining its relative information.

So it would be something like:

John	02/04/2014 18:46:00	02/04/2014 18:46:00	ABKI 1000	140
John	02/04/2014 18:46:00	02/04/2014 18:46:00	ABKI 2000	170
John	02/04/2014 18:46:00	02/04/2014 18:46:00	ABKI 3000	200
Mark	02/04/2014 18:46:00	02/04/2014 18:47:00	ABKI 1000	200
Mark	02/04/2014 18:46:00	02/04/2014 18:47:00	ABKI 2000	230
...

and so on...

Do you think you can help?

Thank you very much !

Cheers

Daniel

Here is the link 

https://docs.google.com/spreadsheets/d/11EetOjUFqOMjvawKKQfztwjYefJtSXD8nQiX0d5Bm14/edit#gid=0

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

Top Contributor Hyde's suggestion of using a script for what you want to accomplish is a good one ... however please also have a look at my following formula based solution

yogi_Working With Source Data As A Formula Output In New versus Old Google Sheets - part 5

                                         Google Spreadsheet   Post  #1516
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-05-2014
post by Bee Lini (https://productforums.google.com/forum/#!mydiscussions/docs/kGuhFo4uWgU)
Multiple QUERY and Select Col help with New Google Sheets
Hi Gurus,

I have a formula, probably not a very efficient or short one, but it use to work with the old sheets. While using the new Google sheets I have not been able to get it to work anymore. I'm really not sure where my mistake in formula is. 

=query(query(index('Copy of Master Data'!B:AE),"select "&join(",","Col"&query(index(if(B1:B29="x",row(B1:B29),)),"select* where Col1 is not null "))), "select* where Col1='"&G2&"' and Col3 <= date '"&text(G4,"yyyy-mm-dd")&"' and Col3 >= date '"&text(G3,"yyyy-mm-dd")&"' ")

Here's the error that results: 
Error: Unable to parse query string for Function QUERY parameter 2: NO_COLUMNCol3


If anyone could please take a look and let me know what I'm doing wrong I would appreciate it. Here's a link: https://docs.google.com/spreadsheets/d/1nx2suoNLcXmnX19ZE2mCTlsPXEdPJenYeXyZdF2zyXE/edit#gid=1448742359
If anyone can figure out a more efficient way to write the formula - I'm all ears!

Thanks
Bee
---
Yogi presented a solution in his blog posts: 

for use in New Google Sheets, see

yogi_Working With Source Data As A Formula Output In New  versus Old Google Sheets - part 4

http://yogi--anand-consulting.blogspot.com/2014/01/yogiworking-with-source-data-as-formula_3573.html

and

for use in Old Google Sheets, see

yogi_Working With Source Data As A Formula Output In New  versus Old Google Sheets - part3

http://yogi--anand-consulting.blogspot.com/2014/01/yogiworking-with-source-data-as-formula_82.html

His solution has led me to another question: I would like to skip the Name data (Column E), so its not repetitive, in the following equation :

=ArrayFormula(query(filter('Master Data'!B:I,('Master Data'!B:B>=G3)*('Master Data'!B:B<=G4)*('Master Data'!E:E= G2)+(row('Master Data'!B:I)=1)),"select "&join(",","Col"&FILTER(row(B:B),B:B="x"))))

I have tried 

=ArrayFormula(query(filter('Master Data'!B:I,('Master Data'!B:B>=G3)*('Master Data'!B:B<=G4)*('Master Data'!E:E= G2)+(row('Master Data'!B:I)=1)),"select "&join(",","Col"&FILTER(row(B:B),B:B="x")), *"skip Col5"))

I have updated my sheet and expected results. Please take a peek!

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

yogi_Search For Occurrence Of Items In A2 to A In Column D Of FormResponses

                                         Google Spreadsheet   Post  #1515
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-05-2014
post by john_nc (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/yk4ddMEFkJ4)
Filter issue with using Wildcards for Cell Data

Thank you ahead of time for any assistance you can offer!!

Issue....  

I have a formula that is set to count data based on information from a certain cell, which is "A2" in this case.

=counta(iferror(filter(FormResponses!D:D,FormResponses!D:D=A2)))

The delima I have is that I want to be able to say if it has any A2 or more infomation. I know you can do this if you were to use Wildcards and exact data like below....

=counta(iferror(filter(FormResponses!D:D,FormResponses!D:D="*Something*")))

With above it would count all sells that had the word Something in it.

Is there a way to modify the first formula I posted to have wild cards so it's counting the words from A2 even if there is more than that information in the cell??

I need it to pull from the cell as it's counting information for multiple rows which are different.

Thanks

John

---

yogi_Compute Difference In Years Months And Days Between BeginDate And EndDate In Cells A1 and A2

                                         Google Spreadsheet   Post  #1514
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-04-2014
post by macman104 and question by paulyhollywood (https://productforums.google.com/forum/#!mydiscussions/docs/b4oNZmPTJRg)
Calculate Years, Month, Days based on Today's date (help modifying an existing solution)
I need to do a similar thing except I need calculate the Years, Month, Days in A3 based on a start date in A1 and a finish date in A2 on each record as a tab on a Google Spreadsheet, how would that differ?
----------------------------------------------------------------------------------------------------------------------------------------------------

yogi_Pull Sum of Items By Type Location And Within A Range Of Dates From Another Sheet

                                         Google Spreadsheet   Post  #1513
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-02-2014
question by GAPS Labrador (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/__PciWDRqBk) 
How do I use DSUM or QUERY or FILTER for totalling a filtered column?

Good day,

Unfortunately, I'm fairly new to more complex formulas, but I've been working on a "database-like" inventory using Forms and Sheets and I have a few needs that would seem to be quite possible, but are somehow alluding me. 

I'm trying to do a fairly straightforward Inventory Summary in which I total the column of products (Column "F" in Inventory adjustments), but only those from a particular storage facility (as defined in column C), in this case "#6 Grenfell."  I've attempted to use SUM, DSUM and QUERY, but I can't seem to get the formula right.  You can see my two attempts on the sheet "Inventory Summary."     Also could you please tell me what's wrong with my formula in cell D9 that it only gives a Zero response?  The sheet is below:

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

I know I'm a bit of a novice at this, but any assistance you could offer would be most appreciated.

Thanks!

K

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

yogi_Find Only Most Recent Entries Per Person From Form Responses

                                         Google Spreadsheet   Post  #1512
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-01-2014
question by Res Net (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/VKmchxjJFGI) 
Finding only most recent values per person for form responses

I am working on a player roster for my Ingress community.  I have an initial sign up form where players input necessary data and I have a different form where players can update some of their info.  My issue is that I only want to use the data from the last update the person submits but I cant seem to figure out how to do it.

My sample data is their name, zone they play in, would they like to be included in the roster, and are they over 18?  I use that info via a vlookup to update their main listing in the roster or, exclude them from it altogether if they choose to opt out or are not 18. (have to protect the young ones and ourselves these days).

The ideal solution would automatically fill as the form is used and more responses pop up on the sheet much like the =continue() that follows arrays such as =filter().  

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

Sample form:  https://docs.google.com/forms/d/1b1rxCZf4m110WW5rfGavhGQI_3sGYsmiAOSZ8p9z9ls/viewform

Any help would be appreciated.

Res

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