yogi_Illustration For Orienting Text Vertically In Google Spreadsheet -- Formula Based Solution

                                         Google Spreadsheet   Post  #1628
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-09-2014

UPDATED Feb-07-2011 (added solution using REGEXREPLACE)

post by: Deb14 -- question by AlexJVar: (https://productforums.google.com/forum/#!mydiscussions/docs/KdyxPSBGjjI)

Man, this is Convoluted!!  How do I add to this post without "Replying" to someone else's post?  

I have tried using the suggested formula:

=ARRAYFORMULA(CONCATENATE((MID( "My Data"; ROW(INDIRECT("YY1:YY"&LEN( "My Data" ))); 1)&CHAR(10))))

And it does not work.  

The text I want to orient vertically is "ADP Websuites 1000 Direct".  I have tried: =ARRAYFORMULA(CONCATENATE((MID( "ADP Websuites 1000 Direct"; ROW(INDIRECT("YY1:YY"&LEN( "ADP Websuites 1000 Direct" ))); 1)&CHAR(10)))), and I have tried: =ARRAYFORMULA(CONCATENATE((MID( ADP Websuites 1000 Direct; ROW(INDIRECT("YY1:YY"&LEN( ADP Websuites 1000 Direct ))); 1)&CHAR(10)))), and all I get is the entire string of characters, all crammed into the cell in which I want it.  

Does anyone know how this works?  Thank you.  

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

sorry you are having difficulty using formula based solution for orienting text vertically in Google spreadsheet -- I hope the following illustration helps

yogi_Search For Names In Another Sheet And Pull Different Columns Associated With Searched Names

                                         Google Spreadsheet   Post  #1627
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-09-2014
post by: Erick Nyaga (https://productforums.google.com/forum/#!mydiscussions/docs/PnYHiAzmUQc)
Apply arrayformula to formula with filter and search functions

Greetings,

I'm using the function below to check a "Key" cell on the same row on Sheet1, search for a match of the contents in the "Key" cell on an entire column on another Sheet2. If found, the formula is able to look across the row where found on Sheet2 and select an item of information to return to Sheet1.

The problem I have is the formula seems pretty heavy and in my case needs to be copied to at least 500x3 rows to pull up specific data matched to the "Key" cell. 

Is there an arrayformula equivalent that can autoexpand down an entire column and return values only matched to the "Key" cell row in Sheet1, that are found on Sheet2?

The logic is that a user can resolve an issue by including the "Key" in a specific cell that the formula matches to the issue to provide selected info.

=index(if(isblank(A9),"", iferror(filter('Resolution Listing'!$B$2:$B,search($A9,'Resolution Listing'!$AF$2:$AF)),"UNRESOLVED")),1)

---

Hi Yogi.

I've introduced some additional dummy data. Please have a look and advise.

Many thanks for your help.

E

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

yogi_Compute Row By Row Amount For Category From Entries In Check Register

                                         Google Spreadsheet   Post  #1626
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-08-2014
post by: Check Register (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/7zzBa5XyvyY)
Summing Drop Down Categories

In the link below, I would like to have the various categories in Column G, to be Summed in Column B using an "iferror" function. I am new to this function and would like any help on how to build this function and/or better understanding of how it works. 

Thanks for any help

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

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

yogi_Mark 'X' In Corresponding Row of Column C If A String In Cells of Column A Appears In A Cell of Column B

                                         Google Spreadsheet   Post  #1625
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-06-2014
post by: Casandra Espinoza (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/9a3d5U3EzYU)
Matching a String in a Cell Against an Entire Column

My goal is to check if a string in the first column, appears somewhere in another column. I want it to be a match if any of the following properties hold:

1. Strings match exactly
2. Strings match regardless of case-sensitivity 
3. Strings match even if only partial

I have created an example to better illustrate my question: https://docs.google.com/spreadsheets/d/1lyGKdR7g8yx2tK4bKGRoxN7AZqsjSq6tbAO0wkpd2hU/edit?usp=sharing 

I would appreciate any help here!

Thank you!

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

yogi_Count Number Of Instances Of A2 to A (in yogi_CommunityServiceEvents) in D2 to D In FormResponses

                                         Google Spreadsheet   Post  #1624
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-05-2014

in response to an earlier post by John_nc:(post by john_nc (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/yk4ddMEFkJ4)
I had provided a solution in my following blog post:
yogi_Search For Occurrence Of Items In A2 to A In Column D Of FormResponses
http://yogi--anand-consulting.blogspot.com/2014/02/yogisearch-for-occurrence-of-items-in.html

---
now John Warf (John_nc) wants to count instances of items in column A  In Column D Of FormResponses
-----------------------------------------------------------------------------------------------------------------------

yogi_Split A1 By Names Separated By Comma While Copying Over The Values From B1 And C1 Into The Split Rows Of A1

                                         Google Spreadsheet   Post  #1623
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-04-2014
post by Saqib Q: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/L2t_KS1fz3k)
How to Split a Cell Part of a Row and Keep the Rest of the Rows - Google Spreadsheets
 am trying to do the following:  I have row 1.  I have values A1, B1, and C1.  A1 contains a few names separated by a comma.  I want to split A1 by these names while copying over the values from B1 and C1 into the split columns of A1.

For example, I have this:

    (A1              B1      C1)
    bob,sam,bill     99      10


I want to have this:


    bob              99      10
    sam              99      10
    bill             99      10


I am also doing this on Google Sheets, I tried using combinations of Split, Join, and Transpose but can't seem to figure it out.  

Any help would be appreciated, thanks.
-----------------------------------------------------------------------------------------------------------------------------

yogi_From Data In OriginalValues Create yogi_OutputWithUniqueValuesInColumnA Of OriginalValues

                                         Google Spreadsheet   Post  #1622
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-01-2014
post by auren22: (https://productforums.google.com/forum/#!mydiscussions/docs/RZbErRJpGm0)
Query or Unique Function to return non duplicate rows from one worksheet to another.
Dear yogi,

Here is my spreadsheet, just that its based on another information, in this case the UNIQUE VALUES should be applied to  column A of sheet "Original Values" and then return the adjacent columns. 

https://docs.google.com/a/shore.com.mx/spreadsheets/d/1DSVSG35uMklahHbjBfS84-Prgb-9MsOcysyNey9TkN8/edit#gid=2000382379

I cannot query the entire sheet "

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

in the following solution I first created a list of TRUE / FALSE values for column A of OriginalValues ... and the I filtered the entire data base with list of TRUE / FALSE as criteria