| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
|
#2
|
|||
|
|||
|
Quote:
Code:
=IF(B5="","",RANK(B$5,$5:$5,1)+COLUMN()/100000) 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))
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))
|
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
|
|||
|
|||
|
Quote:
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) Last edited by GoBow777 : December 19th 07 at 06:43 PM |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
|
|||
|
|||
|
Quote:
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) 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))
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))
|
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
|
|||
|
|||
|
Dave:
This is what I have based on my understanding of your description. Quote:
|
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
|
|||
|
|||
|
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 Quote:
|
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can I rotate data in Excel table (rows to columns, and vv)? | Excel Discussion (Misc queries) | |||
| Using data in rows in a table in columns. | New Users to Excel | |||
| How do I create a link between columns from a data chart? | Excel Discussion (Misc queries) | |||
| Change data to appear in rows instead of columns (reverse a table. | Excel Discussion (Misc queries) | |||
| How to get pivot table data columns instead of rows | Excel Discussion (Misc queries) | |||
Linear Mode
