View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Herbert Seidenberg
 
Posts: n/a
Default sort 2 or more occurrences

Convert the addresses (modified for readability)
into numbers and add headers as shown:
bin2 bin3
111222333444 333222333444
111222333444 111222444555
111222333555 111222333555
111222444555 111222333444
333222333444 0
0
123123321321 0
111222444555 0
333222333444 0
111222333555 0
1115 0

Name the columns bin2 and bin3.
The header <bin2 is located at A1.
In the first cell of bin3, enter this array formula
=MAX(bin2*(COUNTIF(bin2,bin2)1))
In the second cell of bin 3, enter this array formula
=MAX(IF((bin2<bin3 2:2)*(COUNTIF(bin2,bin2)1),bin2,0))
Then drag the fill handle until zeros appear.
Note: Each cell gets its own CTRL+SHIFT+ENTER
Credit: Harlan Grove