function question

  • Thread starter Thread starter Cletus Stripling
  • Start date Start date
C

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
 
Needs VBA


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

for column E

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
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
 
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.
 
Back
Top