 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / August 2007Search lowest value in a column and display the entire row
I have the following cells: Country Code Rate Carrier Albania 355 .10 abc Albania 355 .12 xbc I need to display the entire row with the lowest "Rate." In the above example, I need to show....Albania 355 .10 abc in a separate worksheet. Additionally, I will have multiple destinations, as stated below: Country Code Rate Carrier Albania 355 .10 abc Albania 355 .12 xbc Albania - Cell 35538 .15 abc Albania - Cell 35538 .16 xyz Which will be sorted by the "Code." If a country has 2 carriers with the same "Code," I need the lowest "Rate" of the 2, to display the entire row in a separate worksheet. > I have the following cells: > [quoted text clipped - 4 lines] > I need to display the entire row with the lowest "Rate." In the above > example, I need to show....Albania 355 .10 abc in a separate worksheet. Use MIN to get the lowest rate. And then use Index & Match to get the other columns. Mangesh > Additionally, I will have multiple destinations, as stated below: > [quoted text clipped - 18 lines] > > - Show quoted text - Assume the source table is in a sheet: x, cols A to D, data from row2 to row5, where col C = rate (source table need not be sorted) In the other sheet, supposing you have the inputs specified in A2:C2, viz: Country Code Carrier Albania 35538 abc Then in D2, array-entered**: =MIN(IF((x!A2:A5=A2)*(x!B2:B5=B2)*(x!D2:D5=C2),x!C2:C5)) will return the required result from the source table. Adapt the ranges to suit. **press CTRL+SHIFT+ENTER to confirm the formula  SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Additionally, I will have multiple destinations, as stated below: > [quoted text clipped - 16 lines] > > I need to display the entire row with the lowest "Rate." In the above > > example, I need to show....Albania 355 .10 abc in a separate worksheet. Perhaps you might be looking for this instead .. Assume the source table is in a sheet: x, cols A to D, data from row2 to 100 (say), where col C = rate (source table need not be sorted) In another sheet, Put in A2, array-enter the formula, ie press CTRL+SHIFT+ENTER to confirm the formula: =IF(COUNTA(x!A2:C2)<3,"",IF(MIN(IF((x!A$2:A$100=x!A2)*(x!B$2:B$100=x!B2),x!C$2:C$100))=x!C2,ROW(),"")) Leave A1 blank Put in B2, normal ENTER: =IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(x!A:A,SMALL($A:$A,ROWS($1:1)))) Copy B2 to E2. Select A2:E2, copy down to E100. Hide away col A. Cols B to E will return the required results, ie only the lines from the source sheet: x with the lowest rates, all neatly bunched at the top. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Additionally, I will have multiple destinations, as stated below: > [quoted text clipped - 16 lines] > > I need to display the entire row with the lowest "Rate." In the above > > example, I need to show....Albania 355 .10 abc in a separate worksheet.
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.