View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Herbert Seidenberg Herbert Seidenberg is offline
external usenet poster
 
Posts: 1,180
Default 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