how can I return entire row for column value returned
I am trying to return the top 3 values from a column in a worksheet to
another worksheet I am able to return the lowest value using index, match and then have been able to used the =sum(small(data),{2} for the 2nd and {3} for the 3rd. However this is only for the one cell I am ranking and want to return the entire row values as well. There are approx 10 other columns of data in the row I am wanting to return, any ideas? |
how can I return entire row for column value returned
Hi!
return the top 3 values.....used the =sum(small(data) How do you equate the top 3 with small? Where is this data? If you want the top 3 are there any duplicates (ties)? If so, how do you want to handle that? Biff "Mary Etta" <Mary wrote in message ... I am trying to return the top 3 values from a column in a worksheet to another worksheet I am able to return the lowest value using index, match and then have been able to used the =sum(small(data),{2} for the 2nd and {3} for the 3rd. However this is only for the one cell I am ranking and want to return the entire row values as well. There are approx 10 other columns of data in the row I am wanting to return, any ideas? |
how can I return entire row for column value returned
I have data put into a worksheet called "Detail" in col C - J where C-I total
to J. I want to pull the lowest 3 times from J plus return the other values from C-I that total to J. I have the formula =INDEX(Detail!C:C,MATCH(MIN(Detail!$J:$J),Detail!$ J:$J,0)) in col C - J which is returning the lowest time for J and the adjoining values in C-I. I can return the 2nd and 3rd lowest times in J using the formula below but can't figure out how to get the adjoining C-I that go with J. Hope this makes sense and thanks "Biff" wrote: Hi! return the top 3 values.....used the =sum(small(data) How do you equate the top 3 with small? Where is this data? If you want the top 3 are there any duplicates (ties)? If so, how do you want to handle that? Biff "Mary Etta" <Mary wrote in message ... I am trying to return the top 3 values from a column in a worksheet to another worksheet I am able to return the lowest value using index, match and then have been able to used the =sum(small(data),{2} for the 2nd and {3} for the 3rd. However this is only for the one cell I am ranking and want to return the entire row values as well. There are approx 10 other columns of data in the row I am wanting to return, any ideas? |
how can I return entire row for column value returned
Try this:
=INDEX(Detail!C:C,MATCH(SMALL(Detail!$J:$J,ROWS($1 :1)),Detail!$J:$J,0)) Copy across 8 columns then down 3 rows. Biff "Mary Etta" wrote in message ... I have data put into a worksheet called "Detail" in col C - J where C-I total to J. I want to pull the lowest 3 times from J plus return the other values from C-I that total to J. I have the formula =INDEX(Detail!C:C,MATCH(MIN(Detail!$J:$J),Detail!$ J:$J,0)) in col C - J which is returning the lowest time for J and the adjoining values in C-I. I can return the 2nd and 3rd lowest times in J using the formula below but can't figure out how to get the adjoining C-I that go with J. Hope this makes sense and thanks "Biff" wrote: Hi! return the top 3 values.....used the =sum(small(data) How do you equate the top 3 with small? Where is this data? If you want the top 3 are there any duplicates (ties)? If so, how do you want to handle that? Biff "Mary Etta" <Mary wrote in message ... I am trying to return the top 3 values from a column in a worksheet to another worksheet I am able to return the lowest value using index, match and then have been able to used the =sum(small(data),{2} for the 2nd and {3} for the 3rd. However this is only for the one cell I am ranking and want to return the entire row values as well. There are approx 10 other columns of data in the row I am wanting to return, any ideas? |
All times are GMT +1. The time now is 03:55 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com