## Sunday, March 26, 2017

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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-26-2017
question by topdog2009:
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.

Thanks

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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-26-2017
question by SATH59:
How to sort a query
Hi

I have this sample doc

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 !!!

## Friday, March 24, 2017

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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-24-2017
question by Nathan_Lee:
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:

Thanks for any help!

## Thursday, March 23, 2017

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

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

# 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