Can I do this with the LARGE function?

[c]

I hope someone can help me with this one. So far, you guys have been a great
help for me, thanks.

I have a table of statistics in a multiple sheet workbook. What I want to do
is leave this table in tact so that Column A is always sorted numerically by
cell contents. In another sheet in my workbook, I want to have a similar
table, but sorted by descending values that are located in column D. With
the large function, I can get the value from one sheet to another, but I
would like to know how to get the rest of the row of data to come with it
into the new worksheet. Column D contains a formula, but all I really need
is for the value to be placed in the new sheet.

Here is what I want to achieve:

Existing worksheet:

4.44 2 2115 0.87
1.23 5 1048 3.25
2.57 11 456 4.65
4.58 3 2345 1.24
..
..
..

There are 1000 rows in this worksheet.

The worksheet I want to generate from the existing sheet will only list the
rows containing the largest 30 values of column D, so it would look like
this:
2.57 11 456 4.65
1.23 5 1048 3.25
4.58 3 2345 1.24
4.44 2 2115 0.87
..
..
..

I know this is probably relatively simple to do, but I haven't had any luck
figuring it out. Any help would be greatly appreciated.

TIA
Chris

 

[Alan Beban]

I hope someone can help me with this one. So far, you guys have been a great
help for me, thanks.

I have a table of statistics in a multiple sheet workbook. What I want to do
is leave this table in tact so that Column A is always sorted numerically by
cell contents. In another sheet in my workbook, I want to have a similar
table, but sorted by descending values that are located in column D. With
the large function, I can get the value from one sheet to another, but I
would like to know how to get the rest of the row of data to come with it
into the new worksheet. Column D contains a formula, but all I really need
is for the value to be placed in the new sheet.

Here is what I want to achieve:

Existing worksheet:

4.44 2 2115 0.87
1.23 5 1048 3.25
2.57 11 456 4.65
4.58 3 2345 1.24
.
.
.

There are 1000 rows in this worksheet.

The worksheet I want to generate from the existing sheet will only list the
rows containing the largest 30 values of column D, so it would look like
this:
2.57 11 456 4.65
1.23 5 1048 3.25
4.58 3 2345 1.24
4.44 2 2115 0.87
.
.
.

I know this is probably relatively simple to do, but I haven't had any luck
figuring it out. Any help would be greatly appreciated.

TIA
Chris

One way is, if the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, array
enter the folowing into a 30-row x 4-column range:

=ArrayRowFilter1(a1:d1000,4,large(d1:d100,31),">")

Alan Beban

 

[Arvi Laanemets]

Hi

I'd prefer ODBC query for this. For case you never have used one:
1. Be sure your table has single header row at top - no cell merging, and
all columns must have a header;
2. Define a named range (p.e. MyTable), wich contains at least all rows from
your data table, with header row included (Insert.Name.Define from menu,
enter name, and range reference (an absolute reference, without any formulas
in it);
3. Save your file;
4. From menu, select Data.GetExternalData.NewDatabaseQuery. In Choose Data
Source window select 'Excel Files*' and press OK. Select your workbook as
data source. Select defined range (MyTable?) as table. Transfer all data
fields to right. Continue;
5. In filter selection window set filter for some always filled column to
'Is Not Null';
6. In sort order selection window set order for column D (column names will
be headers from header row)
7. Finish. When asked, point the left upper cell for returned query table.

Whenever you select any cell with returned data, you can refresh the query
(Data.RefreshData from menu, or '!' button on standard toolbar.

When you activate any cell with returned data, you can select
Data.GetExternalData.DataRangePropertis. From Properties window you have a
lot of settings. Btw. you can set the query to be refreshed every time the
workbook is opened, or after every ## minutes.