Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function question
Needs VBA
Rows(Application.Min(Columns(5))).Copy for column E -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Cletus Stripling" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function question
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function question
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum Product Function Question | Excel Discussion (Misc queries) | |||
DB (depreciation) function Help question | Excel Worksheet Functions | |||
clock | Excel Worksheet Functions | |||
INDIRECT function question | Excel Worksheet Functions | |||
Function question | Excel Worksheet Functions |