software

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 VBARows(Application.Min(Columns(5))).Copy

for column E

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

quot;Cletus Striplingquot; gt; wrote in message
...
gt; Is there a way in Excel to use the Min function to determine the minimum
gt; value in a column, and copy that entire row to a new area of the
gt; spreadsheet?
gt;
gt; For example,
gt;
gt; In my sheet I have multiple columns of data. I want to determine the
gt; smallest value in column c but then I want to copy that entire row of
gt; data to a new area.
gt;
gt; 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)),quot;$quot;,quot;quot;) the column
letter
In A14, put =INDIRECT(A13 amp; $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 Studentquot;Cletus Striplingquot; wrote:

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

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