Top 10 Values
 

Hi All,

Assume I have two excel files. I'm having a file with the following
values in a workbook called First.xls under worksheet called 'data'. I
need to find the top 5 values and populate these values in a another
workbook(second.xls) in a worksheet called 'top 5 values'. How it can
be done? is it thru VBA or just thru functions like LARGE? Please help
me, as I'm not an expert in Excel.

Thanks
Easwara.

Data:

A1:A10
28
0
8
1
27
23
8
211
43
33

Top 10 Values
 

His Easwara,

It can be done with something like this.

Sub Top5toNew()
Sheets("Sheet1").Range("a1:a10").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending
Range("a1:a5").Copy
Windows("second.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues
End Sub

Regards,
P

--
patrickcairn
-----------------------------------------------------------------------
patrickcairns's Profile: http://www.excelforum.com/member.php...fo&userid=3179
View this thread: http://www.excelforum.com/showthread.php?threadid=51521

Top 10 Values
 

Hi Patrick,

I'm new to VBA. I tried to run the code which you have sent.Should I
run this code thru VB editor in excel? Also, the files I get will come
in different row length. say first time the file comes with 100 rows,
where I find top 5. next time it comes with 344 rows. So, I need to
take into account this requirement too. we need to find the non-empty
rows in cell A, and then find the top 5 values.

Can you please explain me stepwise, what should I do to execute this
code perfectly? I've first.xls,second.xls c: drive.

Thanks
Easwara

Top 10 Values
 

Easwara,

Can you not just use built in Excel filter? What I mean is; add a title row
to your data, 'Data-Filter-Auto Filter' on that cell/row - click the drop
down select 'Top 10' - change the 10 to a five hit OK & then you can copy
paste the result.

You could record that & tinker to automate - benefit would be that it does
not change you initial data (other than adding the title row).

Regards,

Chris.
--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


" wrote:

Hi All,

Assume I have two excel files. I'm having a file with the following
values in a workbook called First.xls under worksheet called 'data'. I
need to find the top 5 values and populate these values in a another
workbook(second.xls) in a worksheet called 'top 5 values'. How it can
be done? is it thru VBA or just thru functions like LARGE? Please help
me, as I'm not an expert in Excel.

Thanks
Easwara.

Data:

A1:A10
28
0
8
1
27
23
8
211
43
33

Top 10 Values
 

Just use LARGE

=LARGE([Book1]Sheet1!$A$1:$A$10,ROW(A1))

and copy down for 4 rows

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

wrote in message
ups.com...
Hi All,

Assume I have two excel files. I'm having a file with the following
values in a workbook called First.xls under worksheet called 'data'. I
need to find the top 5 values and populate these values in a another
workbook(second.xls) in a worksheet called 'top 5 values'. How it can
be done? is it thru VBA or just thru functions like LARGE? Please help
me, as I'm not an expert in Excel.

Thanks
Easwara.

Data:

A1:A10
28
0
8
1
27
23
8
211
43
33