Return the data from 3 columns

[louiscourtney]

Help please

I have a spread sheet that records amonst other stuff scores, what I'm after
is something that will review the data in column P3 down to P220 choose the
10 highest I then want it to pick up the name from the corresponding line
held in column A 3 down to A220 and also to return the same from column B3
down to B220

the result needs to be placed in a new tab and be displayed over 3 cells by
10 rows
Name Year points
1 shane 2007 500
2 peter 2007 400
3 shane 2006 385
4 paul 2007 368
5 peter 2005 300
6 roger 2006 298
7 steve 2007 287
8 fred 2005 251
9 shane 2005 232
10 harry 2007 221

 

[CLR]

Data > Filter > Autofilter....then select the Arrow at the top of P and
choose "Top 10", then copy and paste the filtered results over to your new
sheet.

hth
Vaya con Dios,
Chuck, CABGx3

 

[louiscourtney]

Thanks for the reply
But if i do that i lose other information i have as the cells are not all
the same size

 

[Loris]

Record a macro that does the following steps:
1. Sorts your main sheet by column P
2. Turns on your autofilter and selects Top Ten for column P
3. Copies A1:A11 from the main sheet to the new sheet.
4. Copies B1:B11 from the main sheet to the new sheet
5. Copies P111 from the main sheet to the new sheet
6. Goes back to the main sheet and turns off the autofilter and sorts it the
way it was originally.
7. Goes back to the new sheet and autofits each column width and leaves you
in whatever cell you wish.
Be sure to assign the macro to a short-cut key or a button on your toolbar.

 

[CLR]

Ok then, try something like this.........

In cell A2 of Sheet2, put this
=LARGE(Sheet1!$P$3:$P$220,1)
Then, copy that formula down to cell A11, and by hand, increase the last
digit in the formula by one for each row until cell A11 is this
=LARGE(Sheet1!$P$3:$P$220,10)

Then in cell B2 of Sheet2, put this, and copy down
=INDEX(Sheet1!$A$3:$P$220,MATCH(A2,Sheet1!$P$3:$P$220,0),1)
Then in cell C2 of Sheet2, put this, and copy down
=INDEX(Sheet1!$A$3:$P$220,MATCH(A2,Sheet1!$P$3:$P$220,0),2)

Then add your header titles on Sheet2. A1= NAME, B1 = YEAR, C1=POINTS

That should do it.

Vaya con Dios,
Chuck, CABGx3