function question

Is there a way in Excel to use the Min function to determine the minimum
value in a column, and copy that entire row to a new area of the
spreadsheet?

For example,

In my sheet I have multiple columns of data. I want to determine the
smallest value in column c but then I want to copy that entire row of
data to a new area.

thanks in advance

Needs VBA


Rows(Application.Min(Columns(5))).Copy

for column E

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Cletus Stripling" <cletus@uga.edu> wrote in message
news:OB6MfvCeGHA.1276@TK2MSFTNGP03.phx.gbl...
> Is there a way in Excel to use the Min function to determine the minimum
> value in a column, and copy that entire row to a new area of the
> spreadsheet?
>
> For example,
>
> In my sheet I have multiple columns of data. I want to determine the
> smallest value in column c but then I want to copy that entire row of
> data to a new area.
>
> thanks in advance

Bob is correct, copying requires VBA. You can use formulae to select the row
with the minimum and automatically link to it. for example:

In A1, put 3
In A2, put 5
In A3, put 6
In A4, put 4
In A5, put 2
In A6, put 7
In A7, put 1
In A8, put 10
In A9, put 8
In A10, put 9

Clearly row 7 has the minimum value for col A.

In A11, put =MIN(A1:A10) this will show the 1
In A12, put =MATCH(A11,A1:A10,0) this will show 7 - the row number
In A13, put =SUBSTITUTE((LEFT(ADDRESS(1,COLUMN()),3)),"$","") the column
letter
In A14, put =INDIRECT(A13 & $A$12) the value

Then just copy A13 and A14 across thru to IV13 and IV14.

Row 14 will always show which row (from 1 to 10) has the minimum value for
column A

--
Gary''s Student


"Cletus Stripling" wrote:

> Is there a way in Excel to use the Min function to determine the minimum
> value in a column, and copy that entire row to a new area of the
> spreadsheet?
>
> For example,
>
> In my sheet I have multiple columns of data. I want to determine the
> smallest value in column c but then I want to copy that entire row of
> data to a new area.
>
> thanks in advance
>

This was useful but not exactly what I want.

For example:

A B C
21 11 9
34 8 6
19 5 17
12 18 21

I then want to look at column B for example, determine minimum value and
then print entire row of data.

So for example, somewhere else in sheet--let's just say in Cell A50 I
want the following:

A50
19 5 17

I want to determine lowest value in specific column and then print
entire row of data elsewhere.


Gary''s Student wrote:
> Bob is correct, copying requires VBA. You can use formulae to select the row
> with the minimum and automatically link to it. for example:
>
> In A1, put 3
> In A2, put 5
> In A3, put 6
> In A4, put 4
> In A5, put 2
> In A6, put 7
> In A7, put 1
> In A8, put 10
> In A9, put 8
> In A10, put 9
>
> Clearly row 7 has the minimum value for col A.
>
> In A11, put =MIN(A1:A10) this will show the 1
> In A12, put =MATCH(A11,A1:A10,0) this will show 7 - the row number
> In A13, put =SUBSTITUTE((LEFT(ADDRESS(1,COLUMN()),3)),"$","") the column
> letter
> In A14, put =INDIRECT(A13 & $A$12) the value
>
> Then just copy A13 and A14 across thru to IV13 and IV14.
>
> Row 14 will always show which row (from 1 to 10) has the minimum value for
> column A
>