ExcelBanter - Link data in rows to columns to create table

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Link data in rows to columns to create table (https://www.excelbanter.com/excel-discussion-misc-queries/170071-link-data-rows-columns-create-table.html)

dartanion

Link data in rows to columns to create table

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

Quote:

Originally Posted by dartanion (Post 600980)

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:

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

Matt

dartanion

Link data in rows to columns to create table

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" wrote:

dartanion;600980 Wrote:
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:
--------------------
=IF(ISERR(LARGE($100:$100,ROW(1:1))),"",OFFSET($A$ 1,4,MID(LARGE($100:$100,ROW(1:1)),FIND(".",LARGE($ 100:$100,ROW(1:1))),6)*100000-1))
--------------------

Matt

--
GoBow777

GoBow777

Quote:

Originally Posted by dartanion (Post 601540)

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:

=IF(B5="","",COUNTIF($5:$5,"<"&B$5)+1+COLUMN()/100000)

dartanion

Link data in rows to columns to create table

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" wrote:

dartanion;601540 Wrote:
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" wrote:
-

dartanion;600980 Wrote: -
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:
--------------------

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

Matt

--
GoBow777
-

Dartanion:

Hmm. Ya, I guess Im 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:
--------------------
=IF(B5="","",COUNTIF($5:$5,"<"&B$5)+1+COLUMN()/100000)
--------------------

--
GoBow777

GoBow777

Quote:

Originally Posted by dartanion (Post 601891)

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:

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

Matt

dartanion

Link data in rows to columns to create table

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" wrote:

dartanion;601891 Wrote:
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:
--------------------
=IF(ISERR(SMALL($5:$5,ROW(1:1))),"",OFFSET($A$1,0, MID(SMALL($5:$5,ROW(1:1)),FIND(".",SMALL($5:$5,ROW (1:1))),6)*100000-1))
--------------------

Matt

--
GoBow777

GoBow777

1 Attachment(s)

Dave:

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

Quote:

Originally Posted by dartanion (Post 602570)

dartanion

Link data in rows to columns to create table

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" wrote:

Dave:

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

dartanion;602570 Wrote:
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|
+-------------------------------------------------------------------+

--
GoBow777

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

Quote:

Originally Posted by dartanion (Post 602889)

dartanion

Link data in rows to columns to create table

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

"GoBow777" wrote:

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, Im pretty
sure Im not going to be of any help to you with this.

Best Regards, Matt

dartanion;602889 Wrote:
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

All times are GMT +1. The time now is 10:24 AM.