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

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

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

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

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

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