function question

[Cletus Stripling]

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 Phillips]

Needs VBA


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

for column E

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Guest]

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

 

[Cletus Stripling]

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.