Microsoft Excel - How to return reference for ranked data based on conditions

Asked By Ewa P on 24-Jul-14 09:21 AM
I need to return the reference numebr form column A so column B that matches condition in cell G1 and returns the largest matching numebr from column C.
i hope the file/pics was succesfully uploaded as i cannot see pics preview.
Harry Boughen replied to Ewa P on 26-Jul-14 03:42 AM
Hi Ewa,
No images so need more info on the data types and layout.  Perhaps you can copy/paste some from your example spreadsheet or take a screen shot and post that.
Regards
Harry
Ewa P replied to Harry Boughen on 28-Jul-14 04:15 AM
excel rank highest.zip
sorry, it appear that i can uplaod files in response to a threat but not when i first write it. please see attached.
thank you
Ewa
Harry Boughen replied to Ewa P on 28-Jul-14 08:00 AM
Hello Ewa,
Have a look at this.  Should get you started.  I can see problems if the scores are not unique.
ewa rank highest.zip
Get back if you need more.
Harry
Ewa P replied to Harry Boughen on 29-Jul-14 06:32 AM
Hi Harry, thank you for that, i iknow what you mean about duplicates. ewa rank highest.zip
ulimately i need to return rank number for every record, but some give me #N/A (column F), those are the duplicates, unfortunatelly in my database i've got over 2000 records so theres quite a lot #n/As. how get around it?
Thank you
Ewa


Harry Boughen replied to Ewa P on 29-Jul-14 08:05 AM
Hello Ewa,
I am going to be travelling for a few days and probably won't get a chance before then but will get back to you asap.
Harry
Ewa P replied to Harry Boughen on 30-Jul-14 06:10 AM
ok, thank you, I'lm away myself till Monday but I'll kep trying to come up with something today.
I've got one more question (may need to move it to new threat but I'll put it here as would struggle again to upload a file)

how to edit the formula below to return not only records from column A that match condition $B$2:$B$262=$I$1 but also in ascending /descending order by records in column D?   (with formula and not sort) ewa rank highest.zip
=((INDEX($A$2:$A$262,SMALL(IF($B$2:$B$262=$I$1,ROW($D$2:$D$262)),ROW($A1:$A1)),0)))

Thank you for help
Ewa
Harry Boughen replied to Ewa P on 02-Aug-14 03:19 AM
Hello Ewa,
Not perfectly clear what you wanted in your last post but here is a version of the file with the tied result 'problem' solved using ColumnH to do the sorting.
Not sure what the significance of the position in ColumnD is - do you want to see records rated 1 to 11 or only the top eleven records regardless of position?
I have included a formula to invert the order because I wasn't sure whether you only wanted the top eleven ascending/descending or whether you wanted the top eleven or the bottom eleven.
ewa rank highest_a.zip

Regards
Harry
Ewa P replied to Harry Boughen on 14-Aug-14 03:46 AM
Thank you, this is working perfectly :)
Ewa