Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Separating equal values
Using the large function I can generate a list of high to low values. example:
In column E1 - E4 =LARGE(B1:B4,1) =LARGE(B1:B4,2) =LARGE(B1:B4,3) =LARGE(B1:B4,4) Next I have the names next to each value: In column D1- D4 =INDEX(A1:A4,MATCH(LARGE(B1:B4,1),B1:B4,FALSE),1) =INDEX(A1:A4,MATCH(LARGE(B1:B4,2),B1:B4,FALSE),1) =INDEX(A1:A4,MATCH(LARGE(B1:B4,3),B1:B4,FALSE),1) =INDEX(A1:A4,MATCH(LARGE(B1:B4,4),B1:B4,FALSE),1) So if my chart was: A B Alpha 10 Bravo 2 Charlie 6 Delta 8 The result would be: D E Alpha 10 Delta 8 Charlie 6 Bravo 2 Here's my problem. Lets go back to the original chart with new values. A B Alpha 10 Bravo 2 Charlie 8 Delta 8 The result would now be: D E Alpha 10 Charlie 8 Charlie 8 Bravo 2 Which omits Deltas score. Is there a way I can tell the index command to, if theres a duplicate value, ignore the previous result? Obviously this is a much simpler version than the one I have at present which contains in excess of 70 names and results. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Separating equal values
Put this formula in C1:
=B1+COUNTIF(B$1:B1,B1)/100 and change the formula in D1 to this: =INDEX(A$1:A$4,MATCH(LARGE(C$1:C$4,ROW(A1)),C$1:C$ 4,FALSE),1) Then copy both these down to get this: alpha 10 10.01 alpha 10 bravo 2 2.01 delta 8 charlie 8 8.01 charlie 8 delta 8 8.02 bravo 2 This is effectively giving you a tie-break so that up to 100 ties could be distinguished, as long as your numbers in column B are integers. If you want to see charlie appear before bravo in the sorted list, then change the + in the first formula to a -. Hope this helps. Pete On Jan 21, 11:49*pm, Darren wrote: Using the large function I can generate a list of high to low values. example: In column E1 - E4 =LARGE(B1:B4,1) =LARGE(B1:B4,2) =LARGE(B1:B4,3) =LARGE(B1:B4,4) Next I have the names next to each value: In column D1- D4 =INDEX(A1:A4,MATCH(LARGE(B1:B4,1),B1:B4,FALSE),1) =INDEX(A1:A4,MATCH(LARGE(B1:B4,2),B1:B4,FALSE),1) =INDEX(A1:A4,MATCH(LARGE(B1:B4,3),B1:B4,FALSE),1) =INDEX(A1:A4,MATCH(LARGE(B1:B4,4),B1:B4,FALSE),1) So if my chart was: * A * * * * * * * *B Alpha * * * * * 10 Bravo * * * * * *2 Charlie * * * * *6 Delta * * * * * * 8 The result would be: * *D * * * * * * * *E Alpha * * * * * *10 Delta * * * * * * *8 Charlie * * * * * 6 Bravo * * * * * * 2 Here's my problem. Lets go back to the original chart with new values. *A * * * * * * * *B Alpha * * * * * 10 Bravo * * * * * *2 Charlie * * * * *8 Delta * * * * * * 8 The result would now be: * *D * * * * * * * *E Alpha * * * * * *10 Charlie * * * * * 8 Charlie * * * * * 8 Bravo * * * * * * 2 Which omits Deltas score. Is there a way I can tell the index command to, if theres a duplicate value, ignore the previous result? Obviously this is a much simpler version than the one I have at present which contains in excess of 70 names and results. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Separating equal values
I answered a similar question earlier today in the General Questions forum.
See this: http://www.microsoft.com/communities...7-78901ecd0fa6 -- Biff Microsoft Excel MVP "Darren" wrote in message ... Using the large function I can generate a list of high to low values. example: In column E1 - E4 =LARGE(B1:B4,1) =LARGE(B1:B4,2) =LARGE(B1:B4,3) =LARGE(B1:B4,4) Next I have the names next to each value: In column D1- D4 =INDEX(A1:A4,MATCH(LARGE(B1:B4,1),B1:B4,FALSE),1) =INDEX(A1:A4,MATCH(LARGE(B1:B4,2),B1:B4,FALSE),1) =INDEX(A1:A4,MATCH(LARGE(B1:B4,3),B1:B4,FALSE),1) =INDEX(A1:A4,MATCH(LARGE(B1:B4,4),B1:B4,FALSE),1) So if my chart was: A B Alpha 10 Bravo 2 Charlie 6 Delta 8 The result would be: D E Alpha 10 Delta 8 Charlie 6 Bravo 2 Here's my problem. Lets go back to the original chart with new values. A B Alpha 10 Bravo 2 Charlie 8 Delta 8 The result would now be: D E Alpha 10 Charlie 8 Charlie 8 Bravo 2 Which omits Deltas score. Is there a way I can tell the index command to, if theres a duplicate value, ignore the previous result? Obviously this is a much simpler version than the one I have at present which contains in excess of 70 names and results. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Separating equal values | Charts and Charting in Excel | |||
Ranking Equal Values | Excel Worksheet Functions | |||
Add different values that equal 1 on a row | Excel Worksheet Functions | |||
Dispay two values in one cell with the / border separating them. | Excel Worksheet Functions | |||
How to add equal values and than replace them with their sum? | Excel Worksheet Functions |