[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
spitinyari
Newbie

Joined: 21 Sep 2011
Posts: 4

 Posted: Wed Sep 21, 2011 2:11 am    Post subject: Getting text from another cell in the row when using LARGE I have a set of data showing how old something is and in the next cell I have the thing's name, a bit like this: 5 Dog 17 Cat 84 Bird 23 Dinosaur 45 Car 12 Bike 56 House I am using a LARGE function on the first column to get the top 3 values, like this: =LARGE(A\$1:A\$7;1) =LARGE(A\$1:A\$7;2) =LARGE(A\$1:A\$7;3) But I also want to get the corresponding bit of text from column B. Is there a way to do this? I have used these forums for a while and can normally find what I'm after, but this time I just don't know what to search for, so thought I'd post. Any help would be greatly appreciated!
Robert Tucker
Moderator

Joined: 16 Aug 2004
Posts: 3407
Location: Manchester UK

Posted: Wed Sep 21, 2011 4:25 am    Post subject:

 Code: =LARGE(A\$1:A\$7;1)&" "&VLOOKUP(LARGE(A\$1:A\$7;1);\$A\$1:\$B\$7;2;0)

Your entries are unique – no two numbers the same?
_________________
OpenOffice 4.0.0 and LibreOffice 4.x.x on Fedora 20, Ubuntu 13.10, Windows 8.1 Preview (Triple Boot)

Last edited by Robert Tucker on Wed Sep 21, 2011 4:41 am; edited 1 time in total
spitinyari
Newbie

Joined: 21 Sep 2011
Posts: 4

 Posted: Wed Sep 21, 2011 4:39 am    Post subject: You, my friend, are a bone fide spreadsheet genius. That was exactly what I was after, thank you!
spitinyari
Newbie

Joined: 21 Sep 2011
Posts: 4

 Posted: Wed Sep 21, 2011 4:42 am    Post subject: I do have some numbers the same. I have done a top 5 and the ones that come in at 3 and 4 are both the same number. When getting the description for rank 4, this seems to be causing it to look at the description cell for entry 3 instead. Is there a way round that? Currently I am seeing something similar to 100 Dog 90 Cat 78 House 78 House 65 Tree but I should be seeing 100 Dog 90 Cat 78 House 78 Frog 65 Tree
Robert Tucker
Moderator

Joined: 16 Aug 2004
Posts: 3407
Location: Manchester UK

Posted: Wed Sep 21, 2011 5:12 am    Post subject:

If you have:

5 Dog
17 Cat
84 Bird
84 Dinosaur
45 Car
12 Bike
56 House

in A1:B7 then enter:

 Code: =RANK(A1,\$A\$1:\$A\$7,0)+COUNTIF(\$A\$1:A1,A1)-1

in (say) E1 and fill down to E7.

In (say) F1 enter:

 Code: =INDEX(A\$1:A\$7,MATCH(ROW(),\$E\$1:\$E\$100,0))

and fill down to F7.

In (say) G1 enter:

 Code: =INDEX(B\$1:B\$7,MATCH(ROW(),\$E\$1:\$E\$7,0))

and fill down to G7.

Not my genius, I'm afraid, see:

http://www.mrexcel.com/archive/Formulas/7938.html
http://www.cpearson.com/excel/Rank.aspx
_________________
OpenOffice 4.0.0 and LibreOffice 4.x.x on Fedora 20, Ubuntu 13.10, Windows 8.1 Preview (Triple Boot)
spitinyari
Newbie

Joined: 21 Sep 2011
Posts: 4

 Posted: Wed Sep 21, 2011 5:38 am    Post subject: Thanks Robert, that's really helpful
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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 vote in polls in this forum