Help please want to write string sorting function

[Duukes]

Hi,

I am new to Visual basic but I have some java experience. I have a single column of data as text strings in an excel doc and I would like to use some code to sort that list and output two columns. the first with unique strings and the second with the frequency of that string in the original list.

here is an example of what i want the code to do

List:
Fruits

apple
apple
apple
melon
orange
apple
grape
orange
apple
melon
grape

output:

Fruit Frequency
apple 5
orange 2
grape 2
melon 2

I have been researching this for the past few days but I can't seem to figure out if it is possible to sort this way in VBA. I know there is a way to do it in java with arrays or array lists. Any help would be much appreciated. I attached a document if you want to see what i mean.

[GarysStudent]

See attached

[mohanvijay]

You can use pivot table

To create pivot table programatically try below code


Dim P_Tbl As PivotTable
Dim L_Rw As Long
L_Rw = ThisWorkbook.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
Set P_Tbl = ThisWorkbook.PivotCaches.Create(xlDatabase, ThisWorkbook.Sheets(1).Range("a1:a" & L_Rw)).CreatePivotTable(ThisWorkbook.Sheets(1).Range("c2"))
P_Tbl.AddDataField P_Tbl.PivotFields("Heading"), "Count Of Heading", xlCount
P_Tbl.AddFields ("Heading")
Set P_Tbl = Nothing

[Teeroy]

GarysStudent's example is a good one if you have excel 2007 or later. Some of the methods don't exist earlier and will then give errors.

[Duukes]

Thanks Garysstudent! That code worked perfectly for what I needed. I am running excel 2010 and have not had any problems with it. I would like to learn more about writing VBA, could anyone tell me a good resource for learning how to write subs for use in excel. I typically learn really well from video tutorials as opposed to books.