top 10 from data table

**batchjb69** · 06-30-2014, 07:02 PM

Hi,

I wish to get the top 10 names and addresses based on their score stored in the data table on the attached. Depending on which person I select in cell C3 of the output sheet. The example shows the output if John was selected.

Is there an event driven macro that can be created to do this.
top 10.xlsx
See attached spread sheet.

Any help much appreciated.

James

**FDibbins** · 06-30-2014, 07:59 PM

For the score (I presume you meant number? there is no "score" heading), use this ARRAY formula, copied down...
=IF(OR(A3 < A1,A3 > C18),0,IF(A3>A2,1000,(A3-A1)*0.01))

However, you have duplicates in your data, how do you want to handle that?

	G	H	I
6	6 Roe Lane, Birkin, Knottingley, North Yorkshire WF11 9LP, UK	982931	Pass
7	39 Blind Lane, Todmorden, West Yorkshire OL14 5HZ, UK	982931	Pass

The rest of the info can be pulled using this...
=INDEX(Input!B$2:B$183,MATCH(Output!$D6,Input!$C$2:$C$183,0))
copied down and across (to the Pass/Fail column)

**batchjb69** · 07-01-2014, 11:37 AM

This doesnt seem to work. ?Is there not a clever way to do this with VBA

**xladept** · 07-01-2014, 12:38 PM

Hi,

Try this:Batchjb.xlsm

**batchjb69** · 07-01-2014, 12:43 PM

This is excellent xladept any chance you could add comments into the code so I can learn what its doing

**batchjb69** · 07-01-2014, 12:53 PM

Actually Ive just realised It seems to work for John but not the others. If you change it to Sylvia the numbers are not in top ten Order

**xladept** · 07-01-2014, 02:26 PM

Hi,

Thanks for the rep!

It's working now just replace the code in your output sheet module:

Please Login or Register  to view this content.

**batchjb69** · 07-01-2014, 05:51 PM

Top10 v2.xlsm

Thanks for looking but I've found your solution has the same issue.

Any Ideas

**xladept** · 07-01-2014, 07:03 PM

Hi Batch,

I just double checked with the file you attached: Dave Starts with 819646 and Ends with 349572 which are the first and tenth Dave entries???

Do we have a failure to communicate here

**batchjb69** · 07-01-2014, 07:11 PM

Its giving the first 10 figures but not the top ten number( by top ten i mean largest numbers) dave should start 977436, 909148 , 903172 etc

Apologies maybe i didnt make this clear.