Summary of cells.

[Madcat]

G'day guys,

Thanks for taking the time at looking at my request.

How do I make the following cells:

Player           FB  WI  CE
FERGUSON, Blake	  2  18   1

Into

FERGUSON, Blake FB, WI, CE

Cheers
Madcat

 

[VBACO]

I am sure there is a cleaner way to do this, but here is a formula that works.

=IF(ISNUMBER(B2),"FB, ","")&IF(ISNUMBER(C2),"WI, ","")&IF(ISNUMBER(D2),"CE ,","")&IF(ISNUMBER(E2),"FE ,","")&IF(ISNUMBER(F2),"HB ,","")&IF(ISNUMBER(G2),"PR ,","")&IF(ISNUMBER(H2),"HK ","")

 

[Madcat]

I am sure there is a cleaner way to do this, but here is a formula that works.

=IF(ISNUMBER(B2),"FB, ","")&IF(ISNUMBER(C2),"WI, ","")&IF(ISNUMBER(D2),"CE ,","")&IF(ISNUMBER(E2),"FE ,","")&IF(ISNUMBER(F2),"HB ,","")&IF(ISNUMBER(G2),"PR ,","")&IF(ISNUMBER(H2),"HK ","")

Thanks so much for the reply VBACO.

The - are actually 0's so how does that affect the formula?

 

[VBACO]

Just change the IsNumber to that expression. For example
=IF(B2>0"FB,","")&IF(C2>0,"WI ".... and so on.

 

[Madcat]

Just change the IsNumber to that expression. For example
=IF(B2>0"FB,","")&IF(C2>0,"WI ".... and so on.

It's come up The formula you typed contains an error.

Thanks again VBACO! Legend!

 

[Madcat]

Round                   FB 	 WI 	 CE 	 FE 	 HB 	 PR 	 HK 	 SR 	 LK 
ZILLMAN, William         7 	 -   	 4 	 10 	 -   	 -   	 -   	 -   	 -   
GORDON, Kevin            -   	 2 	 -   	 -   	 -   	 -   	 -   	 -   	 -   
MICHAELS, Steve          -   	 17 	 3 	 -   	 -   	 -   	 -   	 -   	 -

Also, what formula do I use to get the max games played at a certain position?

ZILLIAM, William  FE
GORDON, Kevin     WI
MICHAELS, Steve   WI

Cheers
Madcat

 

[VBACO]

This formual works for me. Try it again.
=IF(B2>0,"FB, ","")&IF(C2>0,"WI, ","")&IF(D2>0,"CE ,","")&IF(E2>0,"FE ,","")&IF(F2>0,"HB ,","")&IF(G2>0,"PR ,","")&IF(H2>0,"HK ","")

I will think about your second question and let you know.

 

[VBACO]

Wow, there has to be a better way for this one. But here is a formula that will work until a better one can be found.

=IF(CELL("col",INDEX(B2:H2,MATCH(MAX(B2:H2),B2:H2,0)))=2,"FB",IF(CELL("col",INDEX(B2:H2,MATCH(MAX(B2:H2),B2:H2,0)))=3,"WI",IF(CELL("col",INDEX(B2:H2,MATCH(MAX(B2:H2),B2:H2,0)))=4,"CE",IF(CELL("col",INDEX(B2:H2,MATCH(MAX(B2:H2),B2:H2,0)))=5,"FE",IF(CELL("col",INDEX(B2:H2,MATCH(MAX(B2:H2),B2:H2,0)))=6,"HB",IF(CELL("col",INDEX(B2:H2,MATCH(MAX(B2:H2),B2:H2,0)))=7,"PR",IF(CELL("col",INDEX(B2:H2,MATCH(MAX(B2:H2),B2:H2,0)))=8,"HK")))))))

 

[Madcat]

I will think about your second question and let you know.

Thank you very much VBACO.

 

[VBACO]

Ok, I have figured out a much cleaner method that I think will work.

=INDEX($B$1:$H$1,1,MATCH(MAX(B2:H2),B2:H2))

Only problem is.. if there are 2 positions matching it will only return the first it encounters. I am more of a VBA guy than an Excel Formula

 

[Madcat]

Ok, I have figured out a much cleaner method that I think will work.

=INDEX($B$1:$H$1,1,MATCH(MAX(B2:H2),B2:H2))

Only problem is.. if there are 2 positions matching it will only return the first it encounters. I am more of a VBA guy than an Excel Formula

Thanks again VBACO but all the results come back as LK. Which is the last position.