Excel Sort Macro

[sasuser2006]

I'm looking for an Excel macro that will sort a column and bring the duplicates to the top. For example...

Worksheet before macro...

ID
1
1
1
4
2
3
5
4
3
5

Worksheet after macro...

ID Count of ID
1 3
1 3
1 3
3 2
3 2
4 2
4 2
5 2
5 2
2 1

The sort will float the IDs with the most instances to the top. Basically the equivalent of a sql statement with an order by the count of the id descending and the id ascending (so the ids stay together). Does anybody have any information of where I can look to get started or can help me get started. Any help would be much appreciated as I have an Excel problem but no VB skills.

Thanks.

 

[SkipVought]

Hi,
Why use VBA?

COUNTIF will give you the values in column B. Then SORT.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[sasuser2006]

Because of the end users. I'm aware that a formula of:

=SUMPRODUCT(--($A1=$A$1:$A$65535))

Gives me the count I'm looking for and I could then sort, but I would prefer to have a macro that the end user just runs and is possibly prompted for the column that they want to sort.

 

[SkipVought]

Turn on your macro recorder and record doing it.

You can use most spreadsheet formulas in the form...

SomeRangeObject.formula = "=SUMPRODUCT(--($A1=$A$1:$A$65535))"

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]