Link data in rows to columns to create table

[dartanion]

I want to display a league table showing first 5 names and their positions in
asscending order. There will be ties some weeks eg 1,1,3,4,5 and position 5
will sometimes be tied so I have left space for 9 possible "winners".
The names will go in H8:H16 and their positions in G8:G16, sorted by
position order.
The names are static in B1:AI1, and their positions vary week to week in
B5:AI5.
eg This week name in G1 is 1st and last week the name in AB1 won.

Anyone out there done this before or have a working solution?

 

[GoBow777]

I want to display a league table showing first 5 names and their
positions in
asscending order.

Paste this formula in cell B100 and copy across to cell AI100.


Code:
--------------------
=IF(B5="","",RANK(B$5,$5:$5,1)+COLUMN()/100000)
--------------------


Paste this formula in cell G8 and copy down.


Code:
--------------------
=IF(ISERR(LARGE($100:$100,ROW(1:1))),"",OFFSET($A$1,0,MID(LARGE($100:$100,ROW(1:1)),FIND(".",LARGE($100:$100,ROW(1:1))),6)*100000-1))
--------------------


Paste this formula in cell H8 and copy down.


Code:

 

[dartanion]

Hi Matt,

Part 1 worked, no problem.

Parts 2 and 3 display#VALUE!

I changed only the ,s to ;s as my excel is set up on continental settings. I
tried both enter and ctrl-shift-enter, but both came up with the same error.

Hope you can come up with a fix.

Kind Regards

 

[GoBow777]

Hi Matt,

Part 1 worked, no problem.

Parts 2 and 3 display#VALUE!

I changed only the ,s to ;s as my excel is set up on continental
settings. I
tried both enter and ctrl-shift-enter, but both came up with the same
error.

Hope you can come up with a fix.

Kind Regards

:
-

Dartanion:

Hmm. Ya, I guess I’m confused. When you say
“positions”, I assumed row 5 is populated with number
values, is this correct? If not, what determines the ranking of those
positions?

Any way, try this formula in place of the one in row 100.


Code:

 

[dartanion]

Hi Matt,

Tried the alternate row 100, same result.

This is the formula in row 5, with B1 being as follows -
=IF(ISERROR(RANK(B4;B4:AI4;1));"";(RANK(B4;B4:AI4;1))) - which is array, and
simply ranks row 4, ignoring any blanks.

Row 1 has a formula which just brings the name from the input page to the
output/presentation page, and B1 is as follows - ='THIS WEEK'!C86

Kind regards
David

 

[GoBow777]

Hi Matt,

Tried the alternate row 100, same result.

This is the formula in row 5, with B1 being as follows -
=IF(ISERROR(RANK(B4;B4:AI4;1));"";(RANK(B4;B4:AI4;1))) - which is
array, and
simply ranks row 4, ignoring any blanks.

Row 1 has a formula which just brings the name from the input page to
the
output/presentation page, and B1 is as follows - ='THIS WEEK'!C86

Kind regards
David

OK dave, I think I have a better understanding.

Delete the contents of row 100 and replace the formula in row 5 with
this one.


Code:
--------------------
=IF(B4="","",RANK(B$4,$4:$4,1)+COLUMN()/100000)
--------------------


Paste this formula in cell G8 and copy down.


Code:
--------------------
=IF(ISERR(SMALL($5:$5,ROW(1:1))),"",OFFSET($A$1,3,MID(SMALL($5:$5,ROW(1:1)),FIND(".",SMALL($5:$5,ROW(1:1))),6)*100000-1))
--------------------


Paste this formula in cell H8 and copy down.


Code:

 

[dartanion]

Hi again matt.

Once again, no problem with row 5, but both the other formulas returned
#VALUE!

Again I changed only the ,s for;s.

I then spotted a reference to A1, so I changed that to B1 as column A
contains Labels, but same response.

Tried the fx button which described the formula as volitile.

If you like the challenge, feel free to have another go, as all I have at
the moment is a formula in row 6 as follows - =IF(B5=$B$21;"WINNER";""), -
B21 is simply the number 1!, which with a bit of colour the word WINNER looks
average to mediocre, but only displays glory for 1ST place!

Regards

David

 

[GoBow777]

Dave:

This is what I have based on my understanding of your description.

Hi again matt.

Once again, no problem with row 5, but both the other formulas returned

#VALUE!

Again I changed only the ,s for;s.

I then spotted a reference to A1, so I changed that to B1 as column A
contains Labels, but same response.

Tried the fx button which described the formula as volitile.

If you like the challenge, feel free to have another go, as all I have
at
the moment is a formula in row 6 as follows -
=IF(B5=$B$21;"WINNER";""), -
B21 is simply the number 1!, which with a bit of colour the word WINNER
looks
average to mediocre, but only displays glory for 1ST place!

Regards

David

+-------------------------------------------------------------------+
|Filename: Winner.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=102|
+-------------------------------------------------------------------+

 

[dartanion]

Hi again Matt.

That is exactly right. Sadly so are the #VALUE! returns in column G & H.

If your view of it shows names and positions, which I assume it does, then
which of my settings are wrong do you think?

Dave

 

[GoBow777]

Dave:

Yes, the ranking positions are in ascending order in column G and the
names associated with those positions are in column H.

Obviously your settings are different then mine; is it your computer or
what version of Excel are you using? Do you know if all the functions
within the formulas are compatible with your Excel?

I have very little experience when it comes to computers, I’m pretty
sure I’m not going to be of any help to you with this.

Best Regards, Matt

Hi again Matt.

That is exactly right. Sadly so are the #VALUE! returns in column G &
H.

If your view of it shows names and positions, which I assume it does,
then
which of my settings are wrong do you think?

Dave

+-------------------------------------------------------------------+
+-------------------------------------------------------------------+

 

[dartanion]

Hi Matt,
Thanks anyway. I am using excel 2003, but like you, computer workings are a
mystery to me. they either work or not! Obviously mine is not. I will have a
mess around with various settings and if I succeed in displaying the hoped
for results, then I will leave a message here.

Kind regards

Dave