# Sort excel column values ββwith maximum occurrences using VB.net

I have an excel file that has columns B1 - B500 (may vary) filled with numbers. For example:

```
![sample data](http://i.stack.imgur.com/zSkLt.jpg)
```

I need the output:

```
![sample output](http://i.stack.imgur.com/nTqEK.jpg)
```

I have this code so far:

```
Sub Max()
Dim i As Long, j As Long
Dim cl As Excel.Range
i = 1
j = 1
For i = sheet.UsedRange.Rows.Count To 1 Step -1
cl = sheet.Cells(i, 2) '## Examine the cell in Column B
If xl.WorksheetFunction.CountIf(sheet.Range("B:B"), cl.Value) > 1 Then
cl.Value = sheet.Cells(j, 3).value 'copy to Column C
End If
j = j + 1
Next i
End Sub
```

What this code does is find duplicates in column B and remove other records from column. Nothing gets written in column C. I want column B to be unedited at the end. Also can't figure out how to achieve sorting here.

Please, help.

0

source to share

1 answer

Well, you can use formulas if you like:

It is very important to use array formulas (Ctrl + Shift + Enter when finished editing the cell), my Excel is Spanish version, so you just need to change: - SI by IF - CONTAR.SI by COUNT.IF

I came up with this solution while thinking about the bubble sorting algorithm . Hope this is helpful for you.

0

source to share