FISO News   Admin's Spanish Holiday Apartment Rental Offer     FISO News   Rooney, Balotelli, Torres, RVP   


Post new topic Reply to topic  [ 9 posts ] 
Author Message
 Post subject: Lookup query
PostPosted: 08 Sep 2011, 23:00 
Offline
FISO Knight
User avatar

Joined: Thu Oct 13 2005
Posts: 16721
Location: moving into fisodas towers
Blog: View Blog (1)
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.


Top
 Profile WWW FPL Team Page  
 
 Post subject: Re: Lookup query
PostPosted: 08 Sep 2011, 23:14 
Online
FISO Knight
User avatar

Joined: Thu Oct 13 2005
Posts: 13399
Location: Location! Location!
Blog: View Blog (2)
FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4
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.


Top
 Profile FPL Team Page  
 
 Post subject: Re: Lookup query
PostPosted: 08 Sep 2011, 23:22 
Offline
FISO Knight
User avatar

Joined: Thu Oct 13 2005
Posts: 16721
Location: moving into fisodas towers
Blog: View Blog (1)
Surprised wrote:
Why not put the name in c2 to begin with?


:lol: that might just work! :oops:

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.....


Top
 Profile WWW FPL Team Page  
 
 Post subject: Re: Lookup query
PostPosted: 08 Sep 2011, 23:25 
Offline
FISO Knight
User avatar

Joined: Thu Oct 13 2005
Posts: 16721
Location: moving into fisodas towers
Blog: View Blog (1)
nope, can't go in C2, might be able to put it in b3 though....


Top
 Profile WWW FPL Team Page  
 
 Post subject: Lookup query
PostPosted: 08 Sep 2011, 23:26 
Offline
Dumbledore
User avatar

Joined: Thu Oct 13 2005
Posts: 5206
Location: Just about here
Blog: View Blog (2)
FS Record: 116th in TFF 05/06
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


Top
 Profile WWW FPL Team Page  
 
 Post subject: Re: Lookup query
PostPosted: 08 Sep 2011, 23:32 
Online
FISO Knight
User avatar

Joined: Thu Oct 13 2005
Posts: 13399
Location: Location! Location!
Blog: View Blog (2)
FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4
barry wrote:
nope, can't go in C2, might be able to put it in b3 though....


A3 is better as it is below the rank and then you can use Hlookup

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?


Top
 Profile FPL Team Page  
 
 Post subject: Re: Lookup query
PostPosted: 08 Sep 2011, 23:36 
Offline
FISO Knight
User avatar

Joined: Thu Oct 13 2005
Posts: 16721
Location: moving into fisodas towers
Blog: View Blog (1)
got it working under b3, but then realised this problem.....


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 :shock:

want to break that down a bit for me - don't quite understand :oops:


Top
 Profile WWW FPL Team Page  
 
 Post subject: Re: Lookup query
PostPosted: 09 Oct 2011, 02:33 
Online
Grumpy Old Man
User avatar

Joined: Tue Aug 10 2010
Posts: 2367
FS Record: FISO Apprentice Champion 2011-2012
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.


Top
 Profile FPL Team Page  
 
 Post subject: Lookup query
PostPosted: 09 Oct 2011, 13:11 
Offline
Dumbledore
User avatar

Joined: Thu Oct 13 2005
Posts: 5206
Location: Just about here
Blog: View Blog (2)
FS Record: 116th in TFF 05/06
Sorry Barry didn't see your reply.

To the score add +(ROW()/10000)


Top
 Profile WWW FPL Team Page  
 
Display posts from previous:  Sort by  
Bookmark and Share
Post new topic Reply to topic  [ 9 posts ] 

All times are UTC [ DST ]


Who is online

Users browsing this forum: No registered users and 0 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to: