Friday, April 20, 2018

yogi_Highlight Duplicate Entries In Column A

Google Spreadsheet   Post  #2430

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-20-2018
Hi,

Can anyone help to have the formulas for conditional formatting where all with same code in a column will be highlighted. Please with a sample sheet.

Thanks.



Tuesday, April 17, 2018

yogi_Rearrange Data of Events and Participants Into Table of Events By Participant And Gender

Google Spreadsheet   Post  #2429

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

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

I would like to list every row that a name appears in

 I have a spreadsheet to organize a track meet.  it goes
Event1  | name 1 | name 2
Event 2 | name 1 | name 3 | name 4
Event 3 | name 2 | name 5

I would like to produce this result
name 1 | Event 1 | Event 2
name 2 | Event 1 | Event 3
name 3 | Event 2
name 4 | Event 2
name 5 | Event 3

the sheet is attached.  would love some help!

yogi_Sort A Column Of Dates In M/D/YYYY Style By Day In Ascending Order

Google Spreadsheet   Post  #2428

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

question by: Sabrina3241
https://productforums.google.com/forum/#!topic/docs/P-F5jAm0waA;context-place=forum/docs
Sort by a column that has mm/dd/yyyy - chronological, not by first digit
My sheet keeps sorting the column by month, instead of mm/dd/yyyy. For example:

3/31/2019
3/31/2019
4/1/2018
4/11/2018
4/15/2019
4/15/2019
4/30/2019
5/1/2018
5/1/2018


I want it in chronological order by date, not by the first number. I cannot share the spreadsheet, but here is my formula:

=query(importrange("link", "Sheet1!A:AS"), "select Col4, Col18, Col19, Col20, Col21, Col22, Col25, Col26, Col27, Col39 where Col18 contains '1' or Col20 contains '1' or Col21 contains '1' or Col22 contains '1' order by Col39 asc format Col39 'mm/dd/yyyy'",0)

Monday, April 16, 2018

yogi_Rearrange Data of Events and Participants Into Table of Events By Participant

Google Spreadsheet   Post  #2427

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

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

I would like to list every row that a name appears in

 I have a spreadsheet to organize a track meet.  it goes
Event1  | name 1 | name 2
Event 2 | name 1 | name 3 | name 4
Event 3 | name 2 | name 5

I would like to produce this result
name 1 | Event 1 | Event 2
name 2 | Event 1 | Event 3
name 3 | Event 2
name 4 | Event 2
name 5 | Event 3

the sheet is attached.  would love some help!


yogi_Rearrange Row By Row Combined Entities In Column B By Date In Column A

Google Spreadsheet   Post  #2426

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-16-2018
I would like to join text from cells in B into a single cell but ONLY IF it matches a part

ADD A REPLY


Saturday, April 14, 2018

yogi_From A DropDown List Of Categories Choose Item Selected And Associated Product And Price

Google Spreadsheet   Post  #2425

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-14-2018
Hi can someone help me. I am trying to create a dependable drop down list in google sheets
For example, I have dropdown lists like the ones specified

Column A .  Column B  Column C . Column D
Apple            Fruit              Juice         $10
Tomato .       Vegetable      Salad        $15
Potato .         Vegetable .    Salad        $15
Chicken        Meat              Meal          $20
Beef              Meat              Meal          $18
Milk               Dairy             Drink          $6


What I would need is if I choose Tomato in column A, The drop down in column B should only show Vegetable Columns C should show only one value Salad and column D dropdown list will have only one value i.e. $15

Is this possible. If yes please help.

yogi_Create A Template For Reporting Data By Specified Month Beginning With Monday In Which First Of The Month Falls

Google Spreadsheet   Post  #2424

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-14-2018
How do you write a function as a range?
In Google Sheets, how do you create a variable range function?

I want to display a data set located somewhere else. So thought to use the INDEX function.
The problem is that I'm creating a template and thus the data set isn't always in the same place. So I thought to write the range for INDEX as an ADDRESS function and a MATCH function. However, no matter what I try I can't make it work.

An example [=index(address(match(J26,B:B),2,4):address(match(J26,B:B)+6,6,4))]

The problem seems to be that a function cannot serve as a variable. The INDEX function returns an error stating that the argument must be a range.

Is there a way to make this work or maybe a better way to get the same result?

Friday, April 13, 2018

yogi_Consolidate Names Of Registrants From Several Columns Into A Single Column

Google Spreadsheet   Post  #2423

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-13-2018
How to Consoliadte Multiple Columns into a Single Column
I have a spreed sheet that is a collection of registration information. Each person who registers is allowed to register more than just himself/ herself. Thus I have several columns with names within each registration. I need to create a master registration which is a single column of all the names registered. Is there a way to do this with Pivot tables. I’m familiar with pivot tables but have not been able to extract this master registration list without cut and pasting the information. I have included a sample of my sheet.


Thursday, April 12, 2018

yogi_How To Handle Field With Mixed Data Type While ORDERing This Field Using QUERY Function

Google Spreadsheet   Post  #2422

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-12-2018
Creating a "Query How To" ... stuck on order by
Hi All,

I'm creating a short and basic 'how to' guide for beginning query users. Ran into an unexpected issue with 'order by' as it's only working when I have a 'where' included. Please see this Sample Workbook, specifically the 'quick reference' tab and the associated number tab #4.

Feel free to add any simple examples if you've found them helpful. I'm trying to think of the basic ones that are used frequently when beginning to work with query. Maybe add an average or something?

Thank you!
Kristi

Wednesday, April 11, 2018

yogi_Create A List Of TRUE(1) And FALSE(0) With A Specified Approximate Distribution Of TRUE And FALSE Entries

Google Spreadsheet   Post  #2421

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-11-2018
how to generate random list of boolean values with certain distribution of true values
In sheets I would like to generate a random list of boolean values, say 100 values, where a certain percentage of those values will be true (ie. approx 60% are True). How can I do this?


yogi_Sum Numbers In Column B if entry in column C matches one in Lookup table when dare in column A is within specified limits

Google Spreadsheet   Post  #2420

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-11-2018
SUMIFS on whole column with multiple criteria including conditionals
Here is an example spreadsheet. I wish to sum the values in column B when the date is between 2018-04-08 and 2018-04-20 AND the letter in column C is in column the lookup table
2018-04-101aLookup table
2018-04-0910ra
2018-04-26100gb
2018-04-021000ec
2018-04-1210000vd
e
0
https://docs.google.com/spreadsheets/d/10ueiZnLRYsd5-65xtlMIPVp0Tya73g8dfjF8rT02Ufg/edit?usp=sharing


the following works in excel
{=sum(sumifs($B:$B,$C:$C, E2:E6,$A:$A,"<=" & DATE(2018,4,20),$A:$A,">="& DATE(2018,4,8)))}

but this doesn't work in Sheets due to sumifs not working in arrayformula
=ARRAYFORMULA(sum(sumifs($B:$B,$C:$C, E2:E6,$A:$A,"<=" & DATE(2018,4,20),$A:$A,">="& DATE(2018,4,8))))
does anyone have any ideas as to how i might get the desired result?

Tuesday, April 10, 2018

yogi_Look For Teams In 'Season 1 Challenger' And Pull Name Of Opponents

Google Spreadsheet   Post  #2419

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

INDIRECT to a range reference in a different sheet doesn't work.

So what I'm trying to do is create a (dynamic) cell range referencing a range in another sheet. This is what I've been trying:

=MATCH($E$2, INDIRECT("'Season 1 Challenger'!B4:'Season 1 Challenger'!B19"), 0)
=MATCH($E$2, INDIRECT(("Season 1 Challenger!" & ADDRESS(7, ((A4 - 1) * 8) + 2)) & ":" & ("Season 1 Challenger!" & ADDRESS(8, ((A4 - 1) * 8) + 2))), 0)
=MATCH($E$2, INDIRECT(ADDRESS(4, ((A4 - 1) * 8) + 2, 1, True, "Season 1 Challenger") & ":" & ADDRESS(19, ((A4 - 1) * 8) + 2, 1, True, "Season 1 Challenger")), 0)

Every single one of these returns the error:

Function INDIRECT parameter 1 value is "Season 1 Challenger'!$B$4:'Season 1 Challenger'!$B$19'. It is not a valid cell/range reference.

When I try the same things, but just making a cell reference instead of a range reference, it works perfectly fine, but I need a range reference.

If it helps, I can share the sheet.