Top 10 Values

[[email protected]]

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

[patrickcairns]

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,
PC

[[email protected]]

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

[Chris Marlow]

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


"[email protected]" 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
>
>

[Bob Phillips]

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)

<[email protected]> wrote in message
news:[email protected]...
> 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
>