| 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))
|
| 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) | |||
Hybrid Mode
