Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match and large fumction
I use this formula
MATCH(LARGE(J$1:J$55,ROW()),J$1:J$55,0 It returns the row numbers which translates to the largest number in the range large to small Problem is if there numbers in the range which are duplicates,it will return the same row number ,I need it to return the row number +1 A1 5 2 A2 6 1 A3 4 4 A4 3 3 A5 3 4 <<<<< should be 5 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match and large fumction
=MATCH(LARGE(J$1:J$55,ROW()),J$1:J$55,0)+COUNTIF($ J$1:$J1,J1)-1
-- --- HTH Bob (change the xxxx to gmail if mailing direct) "Rowland" wrote in message ... I use this formula MATCH(LARGE(J$1:J$55,ROW()),J$1:J$55,0 It returns the row numbers which translates to the largest number in the range large to small Problem is if there numbers in the range which are duplicates,it will return the same row number ,I need it to return the row number +1 A1 5 2 A2 6 1 A3 4 4 A4 3 3 A5 3 4 <<<<< should be 5 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match and large fumction
=MATCH(LARGE(J$1:J$55,ROW()),J$1:J$55,0)+COUNTIF($ J$1:$J1,J1)-1
works with the original list, but not with this: 5 6 4 3 3 1 1 2 5 1 9 5 9 7 7 8 4 9 =MATCH(LARGE(J$1:J$55,ROW()),J$1:J$55,0) works if a small number, say RAND()/10000, is added to all the numbers with Copy Paste Special Add Duplicate numbers will have different row references, but not necessarily in increasing order. The results might look like this: 11 18 13 16 14 15 2 1 12 9 17 3 4 5 8 7 10 6 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match and large fumction
Bob Phillips wrote:
=MATCH(LARGE(J$1:J$55,ROW()),J$1:J$55,0)+COUNTIF($ J$1:$J1,J1)-1 The range is the first column,the formula in the second column dragged down is MATCH(LARGE(M$1:M$55,ROW()),M$1:M$55,0)(it must start in the first row) 26 is returned because its the row number of 112 (the highest number in the range) I should have 55 unique numbers in the second column with no duplicates?I don't because there are duplicates in the first column. 85 26 98 35 91 42 92 45 103 19 97 5 87 5 95 16 101 16 98 9 82 43 93 27 91 27 94 27 98 2 102 2 95 2 83 2 104 2 94 6 78 6 98 6 83 8 91 8 79 14 112 14 99 14 90 12 87 4 94 4 90 3 97 3 83 3 98 3 110 3 90 28 91 28 91 28 99 7 103 7 102 1 107 46 100 18 92 18 106 18 84 11 80 47 99 25 97 21 56 50 37 52 38 51 31 53 20 54 15 55 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match and large fumction
Here is a way that preserves the row sequence of duplicates.
Assume your list of numbers in named Bin1. Insert Name Define Names in Workbook Bin2 Refers to: =Bin1-ROW()/10000 Next to Bin1, select 55 cells and enter this array formula (Shift+Ctrl+Enter): =MATCH(LARGE(Bin2,ROW()),Bin2,0) The result will be: 26 35 42 45 19 5 40 16 41 9 43 27 39 48 2 10 15 22 34 6 32 49 8 17 14 20 30 12 4 44 3 13 24 37 38 28 31 36 7 29 1 46 18 23 33 11 47 25 21 50 52 51 53 54 55 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match and large fumction
thanks Herert,I never would have that of doing it that way.
Herbert Seidenberg wrote: Here is a way that preserves the row sequence of duplicates. Assume your list of numbers in named Bin1. Insert Name Define Names in Workbook Bin2 Refers to: =Bin1-ROW()/10000 Next to Bin1, select 55 cells and enter this array formula (Shift+Ctrl+Enter): =MATCH(LARGE(Bin2,ROW()),Bin2,0) The result will be: 26 35 42 45 19 5 40 16 41 9 43 27 39 48 2 10 15 22 34 6 32 49 8 17 14 20 30 12 4 44 3 13 24 37 38 28 31 36 7 29 1 46 18 23 33 11 47 25 21 50 52 51 53 54 55 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Match function with duplicate values in an array | Excel Worksheet Functions | |||
Is there a way MATCH() can cope with duplicate values? | Excel Worksheet Functions | |||
Large Index Match Lookup | Excel Worksheet Functions | |||
Match, Index, Vlookup, Large....Help Please | Excel Worksheet Functions | |||
Match, Index, Vlookup, Large....Help Please | Excel Worksheet Functions |