yogi_Make Up Teams of Specified Size By Randomly Selecting Players From A List

Google Spreadsheet   Post  #2140

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-26-2017

question by topdog2009:

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/aBYNEq70AMk;context-place=forum/docs
Choosing Teams from a Range
With a group of people playing online games. :Looking for a way to randomly choose teams of two from a group of names.

Spreadsheet link https://docs.google.com/spreadsheets/d/10d6h7GS_kf-vjjxot3MZ5VU9t_KJp_MEG5jmuv9mQG0/edit?usp=sharing

Thanks

yogi_In Query Formulation By OP Sort By List (PRO CAP CLI DOCREP REC)

Google Spreadsheet   Post  #2139

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-26-2017

question by SATH59:

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/1w_Ma70EteE;context-place=forum/docs
How to sort a query
Hi

I have this sample doc 

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

Im using a Query in Tab Query

=query(arrayformula({
left(Pro!A2:A,3),Pro!A2:D,if(Pro!E2:E="","",to_date(value(Pro!E2:E))),Pro!F2:F;
left(Cap!A2:A,3),Cap!A2:D,if(Cap!E2:E="","",to_date(value(Cap!E2:E))),Cap!F2:F;
left(Cli!A2:A,3),Cli!A2:D,if(Cli!E2:E="","",to_date(value(Cli!E2:E))),Cli!F2:F;
left(Doc!A2:A,3),Doc!A2:D,if(Doc!E2:E="","",to_date(value(Doc!E2:E))),Doc!F2:F;
left(Rep!A2:A,3),Rep!A2:D,if(Rep!E2:E="","",to_date(value(Rep!E2:E))),Rep!F2:F;
left(Rec!A2:A,3),Rec!A2:D,if(Rec!E2:E="","",to_date(value(Rec!E2:E))),Rec!F2:F
}),"Select Col2,Col3,Col4,Col5,Col6,Col7 where Col5 <>'' and Col5 contains '"&B1&"' order by Col1, Col6 desc,Col3 ",0)

So this query is sorted by col 1 and it works, but i allways get the first column sorted by A to Z

Cap, Cli, Doc, Pro, Rec, Rep

What i need is to have it that whay but i want it to sort in different order, for example

First "Pro", then "Cap", then "Doc" then "Rep" then "Cli" ... i want to be able to sort them A to Z but in a different category order

I hope im explainig this well 

How can i achieve this ?

Thanks !!!

yogi_Combine An ARRAY Formula And A Vertical COUNTIFS Formula (per OP's spec)

Google Spreadsheet   Post  #2138

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-24-2017

question by Nathan_Lee:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/OIX0XP0lmms;context-place=forum/docs
How do I combine an ARRAY Formula and a vertical COUNTIFS formula?

I am trying to count the number of identical addresses/zip-codes that occur throughout their corresponding columns.  I can do this through a simple COUNTIFS formula, but as the spreadsheet grows, I need it to build out on its own.

=COUNTIFS(C:C,C2,D:D,D2)

I have attempted a corresponding Array Formula, but I don't know what I need to add/change in order to get it to count properly.  Any help would be appreciated!

=ARRAYFORMULA(IF(ROW(A:A)=1,"ARRAY - # of Events Here",IF(ISBLANK(B:B),"",COUNTIFS(C1:C,C1,D1:D,D1))))

The core of the formula is:

=ARRAYFORMULA(IF(ISBLANK(B:B),"",COUNTIFS(C1:C,C1,D1:D,D1)))

Below is a link to an example spreadsheet that shows what I am trying to accomplish:

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

Thanks for any help!

yogi_Compute Row By Row Time Worked by Team (T) Or Self (S)

Google Spreadsheet   Post  #2137

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-23-2017

question by LiquidLight:

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

How to convert a column specific formula to an array formula?

I'm trying to convert a column specific formula to an array formula. I've having some difficulty and hoping someone out there can help :)

Row 1 houses my headings (and a few other array formula I've converted from column specific formula) but I'm stumped by this one.

This formula currently has to sit in each cell in column O (except O1 which is the heading):

=if(AND(count(J2:N2)=1,E2=0),"S",if(AND(count(J2:N2)>1,E2=0),"T",if(AND(count(J2:N2)>0,E2>0),"T",)))

E are hours worked by me on a specific job (new job on a new row)

J:N are the hours worked by other staff on the same job - it doesn't matter who they are, J:N just allows 5 cells for up to 5 other staff to be included in the same job (it may be these are all empty, it may be that only one or two have values) 

O is where I want this array formula to return whether the other staff have worked solo "S" on the job or teamed "T" with each other or with me.

"S" will show when COUNT(J2:N2)=1 and E2=0

"T" when either COUNT(J2:N2)>1 and E2=0     or     COUNT(J2:N2)>1 and E2>0

The formula above does this nicely... until I try to make it an array formula with the heading "Solo or Teamed?"

I'm getting stumped by the COUNT function I think. I started with this:

=ArrayFormula( if(row($A:$A)=1,"Solo or Teamed",if( len($A:$A),if( AND(count($J:$N)=1,E:E=0),"S",if( AND(count($J:$N)>1,E:E=0),"T",if( AND(count($J:$N)>0,E:E>0),"T",))),)))

...and progressively I got more and more lost!

Any help would be warmly received.

Cheers