got a big spreadsheet with scores and shite, too big to print off/too complicated to email round, so basically trying to get a summary table done.
to keep it simple lets say I'm using Cells A2, B1 and B2. the rank is in A2, the score in B2 and the name in B1, i want to be able to create a wee table on a separate sheet where column A shows 1-16 (number of players) Column B returns the score based on the rank (HLookup I think) but can't for the life of me think how to get Column C to return the name...
cheers in advance.
Lookup query
- barry
- FISO Knight
- Posts: 18877
- Joined: 13 Oct 2005, 18:43
- Location: moving into fisodas towers
- FPL:
- Contact:
- Surprised
- FISO Jedi Knight
- Posts: 26528
- Joined: 13 Oct 2005, 18:32
- Location: Home
- FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4
- FPL:
Re: Lookup query
Why not put the name in c2 to begin with?
Assuming that's not possible and assuming each rank is unique (ie only 1 player can be ranked 5) then I think it can be solved with MATCH+INDEX.
Ie ask it where rank 5 is (A2 in this case) and then the correct formula will go up a row and a column to the right to select the name.
I can't do an example now as I'm too knackered to think straight but I can look tomorrow if no one has solved your problem by then.
Assuming that's not possible and assuming each rank is unique (ie only 1 player can be ranked 5) then I think it can be solved with MATCH+INDEX.
Ie ask it where rank 5 is (A2 in this case) and then the correct formula will go up a row and a column to the right to select the name.
I can't do an example now as I'm too knackered to think straight but I can look tomorrow if no one has solved your problem by then.
- barry
- FISO Knight
- Posts: 18877
- Joined: 13 Oct 2005, 18:43
- Location: moving into fisodas towers
- FPL:
- Contact:
Re: Lookup query
that might just work!Surprised wrote:Why not put the name in c2 to begin with?
it's an existing spreadsheet where c2 is occupied, but I suppose inserting a row shouldn't fk up the formulae..... will have a look.....
- barry
- FISO Knight
- Posts: 18877
- Joined: 13 Oct 2005, 18:43
- Location: moving into fisodas towers
- FPL:
- Contact:
Re: Lookup query
nope, can't go in C2, might be able to put it in b3 though....
- Mystery
- FISO Knight
- Posts: 13816
- Joined: 13 Oct 2005, 18:33
- Location: Just about here
- FS Record: 116th in TFF 05/06
- FPL:
- Contact:
Lookup query
Your problem might be dealing with ties for position in which case I'd add to their score a tiny fraction (1/10,000th) of their row number just to differentiate
- Surprised
- FISO Jedi Knight
- Posts: 26528
- Joined: 13 Oct 2005, 18:32
- Location: Home
- FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4
- FPL:
Re: Lookup query
A3 is better as it is below the rank and then you can use Hlookupbarry wrote:nope, can't go in C2, might be able to put it in b3 though....
is the score in the summary sheet the same score as in your first sheet as mentioned above?
or is it a different score based on the rank?
- barry
- FISO Knight
- Posts: 18877
- Joined: 13 Oct 2005, 18:43
- Location: moving into fisodas towers
- FPL:
- Contact:
Re: Lookup query
got it working under b3, but then realised this problem.....
you're some guy Mystery
want to break that down a bit for me - don't quite understand
Mystery wrote:Your problem might be dealing with ties for position in which case I'd add to their score a tiny fraction (1/10,000th) of their row number just to differentiate
you're some guy Mystery
want to break that down a bit for me - don't quite understand
- crispybits
- Dumbledore
- Posts: 5499
- Joined: 10 Aug 2010, 09:18
- FS Record: FISO Apprentice Champion 2011-2012, Xpert11 FBT winner season 7
- FPL:
Re: Lookup query
Easiest way (not necessarily the most efficient way) to do the coding for that Barry would be to add a column, it has 1,2,3,4,5 all the way down, then add that colum multiplied by 0.0001 to the total score in each row. You'd always have the same ordering on the tie-break that way but you can always easily manually adjust it if needed by swapping the numbers around in the created column for the two tied players.
I'm sure someone will give you a more technical 1 cell way of doing it but that's the way I'd use because I'm lazy, it would work, and it wouldn't require complicated coding.
Example:
Assume column B is total score, and column C is used, column D becomes 1,2,3,4,5,etc all the way down, and in the total score you change it to XXX+(D2*0.0001) then adjust the D column manually to put the tie breaks in the order you want them.
I'm sure someone will give you a more technical 1 cell way of doing it but that's the way I'd use because I'm lazy, it would work, and it wouldn't require complicated coding.
Example:
Assume column B is total score, and column C is used, column D becomes 1,2,3,4,5,etc all the way down, and in the total score you change it to XXX+(D2*0.0001) then adjust the D column manually to put the tie breaks in the order you want them.
- Mystery
- FISO Knight
- Posts: 13816
- Joined: 13 Oct 2005, 18:33
- Location: Just about here
- FS Record: 116th in TFF 05/06
- FPL:
- Contact:
Lookup query
Sorry Barry didn't see your reply.
To the score add +(ROW()/10000)
To the score add +(ROW()/10000)
View Latest: 1 Day View Your posts