Top 10 Values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

easwara@gmail.com - 22 Feb 2006 07:04 GMT

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

Reply to this Message

patrickcairns - 22 Feb 2006 07:55 GMT

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

Reply to this Message

easwara@gmail.com - 22 Feb 2006 08:38 GMT

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

Reply to this Message

Chris Marlow - 22 Feb 2006 08:42 GMT

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.

Signature

Chris Marlow
MCSD.NET, Microsoft Office XP Master

> Hi All,
>
[quoted text clipped - 21 lines]
> 43
> 33

Reply to this Message

Bob Phillips - 22 Feb 2006 09:07 GMT

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)

> Hi All,
>
[quoted text clipped - 21 lines]
> 43
> 33

Reply to this Message