yogi_Split Entries In Column A Into Different Columns Beginning With 1 And Successlvely Increasing By 1

                                         Google Spreadsheet   Post  #1667
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-11-2014
post by DrFriFox: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/PI3CdCruSj8)
Split data from one column into 3 other columns?

I have a huge dataset in one column (A:A), which is actually a collection of smaller datasets that spread only 3 cells.

A:A---
1-A1-B1-C2-A2-B2-C3-A3-B3-C

I need to somehow split it into 3 different columns on the same sheet (into B:B, C:C, D:D), example:

B:B | C:C | D:D
---------------
1-A | 1-B | 1-C2-A | 2-B | 2-C3-A | 3-B | 3-C

This is so then I can reference samples by B:B - the 1st values in each set, C:C - the 2nd values in each set, and D:D - the 3rd values in each set for simple functions such as =average(X:X), =minimum(X:X), and =maximum(X:X) somewhere else in the spreadsheet.

I'm at a complete loss on how to accomplish this. Any tips will be greatly appreciated!

Thanks!

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

yogi_Pull From 'Value List' Those values That Lie Between The Instance of 'Selected value 1' and 'Selected Value 2'

                                         Google Spreadsheet   Post  #1666
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-11-2014
post by JFC111: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/kUKFTj3TtjQ)
Return a series of values between 2 references

https://docs.google.com/spreadsheets/d/1qpoiCAvMAgC4AhQrrUj8RyC5xp0UHRRE0innHDp6jzY/edit?usp=sharing

In my sheet I start by selecting Value 1. Then I select Value 2

The Desired Results should return all values in sequence that are equal to or greater than selected value 1 and equal to or less than selected value 2

What would the formula be in CELL A5 that returns the list?

Thanks

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

yogi_Compute Average Of Last Specified Number Of Non-Zero Responses in 'Days To Response' Column

                                         Google Spreadsheet   Post  #1665
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-11-2014
post by DB Spork: (https://productforums.google.com/forum/#!mydiscussions/docs/oT0N3WNKHGM)
Average last 5 non-zero entries in a column above the current row
Hi everyone,

In my sheet I have column H containing "days to response" data. Some rows have been responded to and have a value like 43, while others have not been responded to and have a 0. (If it's easier I can change it to return "" rather than 0 for items not responded to if that makes the formula easier)

In column J I would like to show the average of the last 5 responses, which means averaging the last 5 non-zero entries above the current row in column H. ie: if this is on row 63 it will look at H2:H63. 
I want to cascade this formula down the entire sheet so each row calculates the average of the previous 5 values in H.


In doing some research I found the following threads but I'm having trouble piecing them together or getting them to work.

https://productforums.google.com/forum/?hl=en#!category-topic/docs/how-do-i/gfOBsk8jQ4Y
https://productforums.google.com/forum/#!topic/docs/Iu4gPSU_mU0
https://productforums.google.com/forum/#!msg/docs/zsqJ-ALWDeM/aMWQEylP0S8J

Thank you in advance for any help!
-----------------------------------------------------------------------------------------------------------------------------------------------

yogi_Compute Row By Row Running Average Of Actual Steps Taken From A Table Of Date Type And Steps

                                         Google Spreadsheet   Post  #1664
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-10-2014
post by Dwight M: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/_pPB_aSFZJo)
How to write a "selective" Array Formula

I would like to use an array formula to calculate a running average of the steps I take each day.  I maintain a sheet (link follows) where I enter my PLAN number of steps for the beginning and end of the month and post my ACTUAL steps each day.  I would like the array formula to ignore any values in the PLAN rows when calculating the running average.

A simplified version of the sheet can be found at this link:

https://docs.google.com/spreadsheets/d/1Jfbe0m0ig_KaathxWyvRIrzUM3JOYFG6KSBE5g_i468/pubhtml

I have a formula in D1 that almost gets me there (thanks to several posts to other questions in this group by Yogi and Adam).  The only problem with this formula is that it includes the values from the PLAN rows when calculating the running average.  The desired outcome is shown in column F, which calculates a running average for the ACTUAL rows only.

Thank you for any suggestions.

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

Yogi_Count Instances of Unique Entry (or entries) In A List

                                         Google Spreadsheet   Post  #1663
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-06-2014
post by Dan Kinchen: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/H9heNIyKYoA)
Count the number of unique string occurrences in a range, keep in mind words within words

I have a sheet named Log with table range of data. In one column I have a list of sprint task types. I have given this the range name of SprintType. My problem is reporting this range.

I have working code, BUT one of my returning counts in my formula is wrong because a word "planned" is contained inside of other words "unplanned".

I only have 3 values.

 - backlog

 - planned

 - unplanned

Table Data Example:

Sprint Type

backlog

backlog

backlog

planned

planned

planned

unplanned

The count should be backlog (30), planned (3), and unplanned (1). Instead I have 4 for planned and 1 for unplanned.

Here is the formula.

=COUNTA(IFERROR(FILTER(Log!SprintType;SEARCH("planned";Log!SprintType))))

thanks!

---

oh and no Queries please. I can solve this with a query but due to my advance reports it is not a good idea.

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

I don't understand why use of QUERY is not a good idea ... in any event have a look at the solution to a bit more generalized problem

yogi_Pull From 'Transaction Log' sheet Data By Client And Rearrange Per Specification

                                         Google Spreadsheet   Post  #1662
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-02-2014
post by Polish user: (https://productforums.google.com/forum/#!msg/docs/mlv8M9M7pIE/otx4ni6p5YwJ)
How to automatically copy data from one sheet to other sheets based on criteria

Hi,

I have been stuck with this issue for a while now. I have been trying to explore some formula / script that could help me copy data from a log, that acts as a database, where daily records are kept, and copy certain cells to other sheets, based on certain matching criteria.

For example, I have a transaction log, where I record daily transactions with certain fixed clients. I have Client A and Client B, that do various transactions daily. I maintain a separate sheet for each client, where I am able to view transactions related to that client only.

Currently, at the end of each day, I manually make reference to each cell on the transaction log, to be able to have this data related to each client on their respective sheet. I am looking for a way, in which data related to each respective client could be copied / referenced automatically, each time I add new data to the Transaction Log.

I am posting the link to the Test page, to be able to better understand the issue. : 

https://docs.google.com/spreadsheets/d/1vSUchCgbvTg7rC6S76GVtashzN0tZpE1lcHcUy7INoU/edit?usp=sharing

Thanks in advance for the help. It would be very helpful  to me.

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

yogi_Pull Select Data Into Another Sheet Except For Specified Columns And Columns With Blank Cells

                                         Google Spreadsheet   Post  #1661
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-01-2014
post by ghorning166: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/0rPm2lLJcAg)
Removing blank cells and certain cells from an array to create a new list

I have a sheet called JOBS that contains about 100 columns, some of which are sums of other cells in the same row. Most of these cells refer to options the customer wants us to do for the job. So, each job has several options but only a few are chosen. Scrolling through the whole width of the JOB sheet is tedius so I would like to create another sheet (call it RESULTS) that condenses the options chosen for any particular business, to one simple list, For every option chosen in the JOBS sheet, the contents of that cell is displayed on the new RESULTS sheet. 

The catch to this is, I don't want any blank cells, I don't want some of the columns (such as subtotal columns), and I want only the most recent job showing up. 

Here is how it might look:

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

JOBS Sheet

Name             Date           ProductA    Item       Subtotal       Service       ItemB

Business 1     5/15/14       Product3                  $44.00       Service88     Item44

Business 2     5/15/14       Product7     Item12   $25.00       Service89     

Business 3     5/17/14       Product2                  $14.00       Service88     

Business 1     5/20/14       Product1     Item15   $35.00                          Item55

Business 5     5/23/14       Product3                  $44.00       Service89     Item22

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

RESULTS Sheet

Name:       Business1     ( I will type the business name in to get the report below that I want)

Date:         5/20/14

ProductA:  Product1

Item:         Item15

ItemB:       Item55

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

Notice above that Business1 has two entries in the JOBS Sheet but I only want the most recent. The subtotal column is skipped and since there is nothing in the Service column, that is also skipped in the report. 

Any help would be appreciated. Thanks 

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