Sorting Max values in a column of data

M

Matt

I have two columns of data (one with a part number the other with # of pcs).
I want to post the top (max) three values (# of pcs) along with its
corresponding part number in another section of the worksheet. How do I do
this?
 
G

Gary''s Student

Sort both columns by the #pcs column descending, then copy and paste the top
three cells.
 
M

Matt

Any way to eliminate the manual 'copy and past' move? I was hoping I could
get the sheet to do this for me.
 
G

Gary''s Student

This is almost as easy as copy/paste. Say the data in cols A & B is:

part#1 1
part#2 8
part#3 18
part#4 2
part#5 13
part#6 10
part#7 9
part#8 3
part#9 4
part#10 7
part#11 6
part#12 20
part#13 12
part#14 16
part#15 11
part#16 17
part#17 14
part#18 15
part#19 5
part#20 19

In E1 enter:
=LARGE(B:B,ROW()) and copy down

In D1 enter:
=OFFSET($A$1,MATCH(E1,B:B,0)-1,0) and copy down

We see:
part#12 20
part#20 19
part#3 18


The neat thing about this approach is that the further down we copy, the
more we see.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with Vlookup and MAX functions 4
How Do I Find Different Values In A String? 2
Finding location (or start cell) of highest consecutive values 3
Making MAX and MIN range variable 1
data in 2nd column associated with =MAX(H1:H100) from another colu 2
Identify the row in which a MAX number in a column resides 7
Sum Values from different column 1
Consolidate two Data Sets for Making a Report 1

Top