load highest value from column to another

**dealer** · 07-22-2010, 12:59 PM

I have uploaded a w/book and hopfully someone will take a look.
I have sales people and there sales amounts organized by location.What would like to do is load the top 20 (or there abouts) sales people&amounts from each loaction.
ex north(Column A = 20 from columns F3 to J8). South (column B = 20 from F15 to J20) and so on.Thanking you.

**DonkeyOte** · 07-22-2010, 01:11 PM

Is this how your "real" file is configured - ie with "sales amounts" stored alongside names in alpha numeric strings ?

If it is - you would really be best served considering a User Defined Function (VBA) as a formula approach will be hideously inefficient.

**dealer** · 07-22-2010, 01:18 PM

No it's generally not but Because there will be a lot of people in different groups i wanted to see could it be done from the example i uploaded.And a VBA solution i would welcome cause then i can study how it works.

**DonkeyOte** · 07-22-2010, 01:44 PM

The below UDF utilises Chip Pearson's QSortInPlace Routine - this is of critical importance given it does the complex stuff
(ie ordering the single dimension Array in reverse allowing you to extract values whilst handling duplicity etc)

UDF:

Please Login or Register  to view this content.

Chip Pearson's code can be found within the attachment link provided on his webpage: http://www.cpearson.com/excel/SortingArrays.aspx

With the UDF and Chip Pearson's code in place your in cell call would simply be:

Please Login or Register  to view this content.

A2:A21 can be copied across but you must of course adjust the sales ranges as appropriate (ie F3:J11 becomes F15:K22 for South etc...)

Hopefully that's along the lines of what you wanted.

**DonkeyOte** · 07-22-2010, 02:11 PM

In retrospect - given the dependence upon CP's code I figured it would be easier to upload your sample file with code in place to avoid potential for confusion.

Hopefully it helps you out.