Sort limited data in a Table

  • Thread starter Thread starter gacollege
  • Start date Start date
G

gacollege

Hey everyone,

Got this one sorting question that is driving me crazy. I need to take
data that is sitting in column F and have it sort only the top five in
Column A. If it just sorts the dollar values, I assume I can get the
customer name and number using a VLOOKUP. I have attached a
spreadsheet with an example.

The reason I want to transport data to column A is that the inputs in
Column F will be pasted into each month and the data will change.


Any help given will be greatly appreciated.

File Attached: http://www.exceltip.com/forum/attachment.php?postid=307693 (question.xls)
 
Assuming you want to extract the top 5 of
the data in F4:H16 by $value (col H) into A4:C8

with:

col A = cust number
col B = cust name
col C = $value

Try putting in:

C4: =LARGE($H$4:$H$16,ROW()-3)

B4: =OFFSET($H$4,MATCH(C4,$H$4:$H$16,0)-1,-1,1,1)

A4: =OFFSET($H$4,MATCH(C4,$H$4:$H$16,0)-1,-2,1,1)

Select A4:C4

Copy down A4:C8 to get the top 5

Hope the above helps.

Max
-----------------------------
Please post replies to the newsgroup

My yahoo email account is out 99.99% of the time
because of the incessant virus-sending-large-email !!

gacollege said:
Hey everyone,

Got this one sorting question that is driving me crazy. I need to take
data that is sitting in column F and have it sort only the top five in
Column A. If it just sorts the dollar values, I assume I can get the
customer name and number using a VLOOKUP. I have attached a
spreadsheet with an example.

The reason I want to transport data to column A is that the inputs in
Column F will be pasted into each month and the data will change.


Any help given will be greatly appreciated.

File Attached: http://www.exceltip.com/forum/attachment.php?postid=307693
Click to expand...
(question.xls)
 
-----Original Message-----
Hey everyone,

Got this one sorting question that is driving me crazy. I need to take
data that is sitting in column F and have it sort only the top five in
Column A. If it just sorts the dollar values, I assume I can get the
customer name and number using a VLOOKUP. I have attached a
spreadsheet with an example.

The reason I want to transport data to column A is that the inputs in
Column F will be pasted into each month and the data will change.


Any help given will be greatly appreciated.

File Attached: http://www.exceltip.com/forum/attachment.php?postid=307693
(question.xls)

------------------------------------------------

~~ View and post usenet messages directly from http://www.ExcelForum.com/

.
I'd like to help, but I don't think attachments are
Click to expand...
available in the forum. Please repost your question with
more detail.
 
Back
Top