yogi_Pull Data In Two Columns Of Existing Table From Data In Another Sheet Of The Same Workbook

Google Spreadsheet   Post  #2409

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Mar-31-2018

question by: Ryan G White
https://www.blogger.com/blogger.g?blogID=8807805591547673516#editor/target=post;postID=4424661971021479208

How to count the number of repeated values for two data points

by Ryan G White

 9:16 AM

ADD A REPLY

I have a list of clients that were acquired by specific team members. I want to be able to track the total number of clients each person has gotten to the deposit stage and the withdrawn stage for training purposes. Here is the link for a test spreadsheet:
https://docs.google.com/spreadsheets/d/1cDrdrAZHf3fIsvwc7d_32Y00t_Z1u8M30SU9Z2jX8VE/edit?usp=sharing
I have also attached two images of the spreadsheet - with the one with three columns of values being the original data and the one with two empty columns being where I want the values to be. The intention would be to have next to Bob's name: in the Deposit column the number 1 and in the withdrawn column the number 2. 

yogi_Find Row By Row If A Phrase In Column B Exists In Column A

Google Spreadsheet   Post  #2408

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Mar-30-2018

question by: GioSico
https://productforums.google.com/forum/#!topic/docs/jX_Oql2okhg;context-place=forum/docs

Search string in Col A for words in Col B and paste found word in Col C
Hi Everyone,

I have been searching for a solution with no luck.  Any help is appreciated.

I have a long list of strings in Column A that have headings I want to extract, like Unit or School or Institute

The words I want to extract are in Column B

I want to past the extracted word in Column C

Both A and B can be a long list so manually typing and searching would be problematic.

My manual version does not use Column B and also fails to match "School" sometimes

"=IF(RegExMatch(A2,"School"),"School", IF(RegExMatch(A2,"Faculty"),"Faculty","Other") )"

Here is my sheet. Thanks in advance.

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

yogi_Compute Row By Row Renewal Dates Every 30 Days Subsequent to Starting Date In Column B

Google Spreadsheet   Post  #2407

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Mar-29-2018

question by: Collin Tovar
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/Mo2476jbqhk;context-place=forum/docs

How to automatically enter 30-day renewal dates?

Hello! I am new to Google Sheets, so bear with me!

I own an Instagram growth agency where my clients renew every 30 days. Not exactly 1 month (which would be easier).

So their renewal dates change every month!

I made a list of all my clients and I have their start dates in there. I just need to see when their renewal goes through every 30 days and resets every 30 days. 

So basically a continuous 30-day countdown, every 30 days, starting from their original date. If that makes sense.

Please help!! Lol idk what I'm doing

I've shared the link to my document below.

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

yogi_ Rearrange Two Groups Of Items In Range A1:H1 Separated By A Blank Cell Into Reange L2:M

Google Spreadsheet   Post  #2406

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Mar-27-2018

question by: Ivan LD
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/YfhzfzFKuO4;context-place=forum/docs

transpose list
Hi im trying to transpose 2 list the first list would get the info from A1 to whatever cell is empty in row 1 so if A1,B1,C1 as value and D1 was empty it would transpose the value of A1,B1,C1 in L1 then the second list would start from the next cell from the empty cell so if D1 was empty and E1,F1,G1,H1 as value then my second transpose list in M1 would have the value of E1,F1,G1,H1

Here's a link to a sample sheet

https://docs.google.com/spreadsheets/d/1r-zQvVjHxFFlT0qmrolvrMCGD2GX049aPoHRMMM_12g/edit?usp=sharing

Thanks

yogi_Using Indirect Function with Conditional Formatting

Google Spreadsheet   Post  #2405

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Mar-25-2018

question by: Callan Cueva
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/05G5J15zAd8;context-place=mydiscussions

Using Indirect Function with Conditional Formatting

I'm trying to color the cells in column G in Sheet3 based on the value of the corresponding cell in Sheet4.

If the value for a cell in Sheet4 is 1, then I'd like the corresponding cell in Sheet3 to be green. If the value is zero, then the color should be set to red.

For example, for G2 Sheet3, "Prom" should be colored green because the value for G2 Sheet4 is 1.

I've been experinmenting with G2 Sheet 3 using:

=indirect("Sheet4!G2")="1"

as a custom formula within conditional formatting, but no luck.

I haven't tried the formula for the red yet, but G5 Sheet3, for example should be red, as the value for G5 Sheet 4 is 0.

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

yogi_ArrayFormula ( VLookup ( Query with dynamic limit clause)))

Google Spreadsheet   Post  #2404

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Mar-23-2018

question by: Tom Stroll
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/YamDGiRzi7k;context-place=mydiscussions

ArrayFormula ( VLookup ( Query with dynamic limit clause)))

Hey there, Happy Friday! :)

****UPDATED SIMPLER SPECS at BOTTOM**** (See my final post from March 23, 2018 )

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

Desired Result in Red (ColE). Orange cell F2 is the closest I got.

=ArrayFormula(if(Len(C2:C),VLOOKUP(C2:C,{Collaborators!C2:D},2,TRUE),""))

Logic:

If a song has 3 composers and 1 publisher, the ArrayFormula would VLookup the 'Collaborators' sheet and return the 1st composer's corresponding publisher name (1 row below on the 'Collaborators' sheet) and that would be the only publisher name we see for that song. The other two would basically be filtered out by the count of publishers for that song.

HOWEVER, if a song has an equal number of composers and publishers, we would see all corresponding publisher names offset more simply by the Count of composers (grouped by that song) so that publisher names ONLY display in Rows where ColB says "Publisher" and Composer Names (Col C and D) are Blank. 

In other words the Greyed out text in ColF should not display because there are more composers than publishers for that Song b and Song e for example.

So, I'd like to replace {Collaborators!C2:D} with a Query that limits the number of Publishers per song to match the actual number of Publishers in ColB for each specific song. 

When I've tried this, I get all the same Publishers or weird offsets and errors.

ColP has all my failed ArrayFormula attempts.

NOTE: I realize Offset may be volatile with large data sets, so i'm hoping Query's limit clause can replace the need for Offset.

Maybe something like... Query(range,  "select ....  group by A  limit='"&indirect(counta())&"' "

Still, I'm using the word 'Offset' now to illustrate my point.

Thanks so much for looking into this! :)

-tom 

yogi-Count Sum Of Instances Of Hourly Times Between 8:00 And 1:00 On Specified Dates

Google Spreadsheet   Post  #2403

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Mar-21-2018

question by: Kelly Sheppard
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/BhFc21pWQpY;context-place=forum/docs
Can you do a filter and countif formula?
I'm looking for a formula that will be able to return a count of occurrences from a list for a certain time frame within a specified date range. The date range needs to be able to change, but the time frames do not (by time frames I mean between 8:00am and 8:59am).

I've attached a sample sheet. I have a formula to filter and count all occurrences but I need to have one that will just return a count for the specified date. I've included that one on the sheet. I'm wondering if you can do a filter with a countif but can't seem to get it to work.

Here is the link

https://docs.google.com/spreadsheets/d/1ut36D6o0bgJZZSdCV-6lYZgJvxPl9iVdONcIJ6wBXbk/edit?usp=sharing

Thank you!

yogi_Row By Row Reverse A Series Of String In Column A

Google Spreadsheet   Post  #2402

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Mar-18-2018

question in: The Bowl and Boolean Hangout

Row By Row Reverse A Series Of Strings In Column A

yogi_Rearrange Data From A Four Item Repeat Single Column Table Into A Four Column Table

Google Spreadsheet   Post  #2401

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Mar-18-2018

question by: Zachary Chandler
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/GlHJmCTU81Q;context-place=forum/docs
Getting more out of auto filling an equation?

yogi_Autofill By Dragging Down Data From A Four Column Table Into A Single Column

Google Spreadsheet   Post  #2400

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Mar-18-2018

question by: Zachary Chandler
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/GlHJmCTU81Q;context-place=forum/docs
Getting more out of auto filling an equation?
Hi there! I'm trying to format some text, and I've set up what I thought was a simple enough pattern for the autofill to pick up on but sadly no dice. I've got a column of information that is in a certain order and I'm trying to format it into simple rows. What I've got right now looks like this

Archimedes Glick
31
179.5
Pilot
Johnta Kamerling
34
235.25
Medical
Nicola Tesla
33
235.15

And I'm trying to make it look like this

"Archimedes Glick",

31,

179.5,

"Pilot",

I'm using the CONCATENATE() function to add the punctuation, and my formulas for the cells are

=CONCATENATE("""",B6,"""",",")

=CONCATENATE(B7,",")

=CONCATENATE(B8,",")

=CONCATENATE("""",B9,"""",",")

This is what I'm using from left to right, the next row has B10, B11, B12, and B13 respectively. I tried filling this row down and it didn't work as I had hoped, I then input it manually for three rows to see if the pattern would click (previous row# + 4)but the results I got were very hit and miss (currently auto filling another row gives B9, B11, B12, B13 which is the right range of data but starts in the wrong place).

How would I go about either getting autofill to just add 4 to the row entry relative to the cell above it? Or, what other functions could I use to describe the behaviour explicitly?

Thanks for your time!

P.S I'm in a computer class with homework that has some seriously tedious data entry aspects and while its taking me more time to figure out how to do it 'smarter' than doing it manually I'm looking at this as learning opportunity to automate future tedious tasks. I tried to google about this issue but didn't pull anything I could use.

yogi_Pull Row By Row Entries In Columns A:K into Column L

Google Spreadsheet   Post  #2399

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Mar-18-2018

question by: Anonymous Gecko
https://productforums.google.com/forum/?utm_medium=email&utm_source=footer#!topic/docs/kVU2ynMJRGA;context-place=forum/docs
How to duplicate a word in a cell located in a whole row of empty cells?

Hi,

As the title says,

I have a whole row ( Ex. A1 to A10 ) of empty cells. Say there is a Data is cell A5, then A11 has to replicate the word located in the row range (A1 - A10).

The Data can be randomly generated anywhere from A1-A10 (using google form), so the output has to be replicated in A11.

Also the same has to generated below to new responses as well using Array formula.

Please help me with this equation.

thanks

yogi_Apply conditional formatting based on string appearing twice inside a cell

Google Spreadsheet   Post  #2398

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Mar-16-2018

question by: Paul Katzman
https://productforums.google.com/forum/?utm_medium=email&utm_source=footer#!topic/docs/KaUHdrshU2Q;context-place=mydiscussions
Apply conditional formatting based on string appearing twice inside a cell
Hi, I have a table of tests run on a batch of files. Y axis are the specific files, and X axis are the tests. Each test is able to be interpreted in two different ways, and rather than double my number of columns I have the results in a cell on multiple lines inside the cell. See below for crappy ASCII diagram.

                        Test 

File 1            Result A

                      Result B

File 2            Result A

                      Result B

File 3            Result A

                      Result B

What I'm trying to do is to only highlight cells that contain the same string value for both Result A and Result B, I don't care about tests where Result A and Result B are different values. How is this possible?

Thanks for the help folks.

yogi_Compute Years And Days For Given Numbers in Column A

Google Spreadsheet   Post  #2397

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Mar-14-2018

question by: David Egan
https://productforums.google.com/forum/#!topic/docs/Tk3fEOQeHg4;context-place=forum/docs
Express a number in Years and Days

On a project I am working on I want to record how old a person was on a given date. Because I have their DOB and the given dates getting the number of days is easy, But I cannot figure out how to turn, for example I would like 11112 to output as "30 years 154 Days"

Any advice would be much appreciated

yogi_From Pivot Table Consolidate Data In Another Table On The Same Sheet

Google Spreadsheet   Post  #2396

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Mar-14-2018

question by: Tony Cannon
https://plus.google.com/102455272676579377322/posts/fdb2C2oFdjD
yogi_From Pivot Table Consolidate Data In Another Table On The Same Sheet 

Hi all

I have an issue with a spreadsheet that I'm hoping you can give me some guidance on.

I help run a snooker competition in the summer and one of the statistics gathered is high breaks. Using pivot tables I split the data down into lists based upon the competition the player is in (Mens, 40+, U21, U16, Billiards)

Now, this works fine BUT when someone has multiple breaks worth recording they are listed multiple times, such as

1 John Farrell Republic of Ireland 132
2 John Farrell Republic of Ireland 104
3 Dean Young Scotland 101
4 Michael Judge Northern Ireland 93

I'd prefer to list them as

1 John Farrell Republic of Ireland 132, 104, 62, 47, 43
2 Dean Young Scotland 101
3 Michael Judge Northern Ireland 93, 77, 56, 47, 43

On this way the breaks table is shorter but keeps more names on display

The sample data set that I have complete with pivot table can be found attached. This sheet has the submissions, a pivot table listing the breaks from the Mens competition and a third sheet where I started playing around with UNIQUE and VLOOKUP.

I'm sure it isn't impossible, but I'm drawing a blank and I'm normally pretty good, probably starting off with the wrong base with UNIQUE/VLOOKUP...

Any help would be greatly appreciated!

Tony C

testing