Macro to sort a list

[vasiliskyj]

would it be possible to sort a list and count how many types are in one list. [TABLE="width: 500"]
<tbody>[TR]
[TD]Colum A[/TD]
[TD]Colum B[/TD]
[/TR]
[TR]
[TD]collision [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]collision [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]auto, property[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]auto property [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]property [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]property [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]property [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]rental [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]rental [/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Would it be possible to select Colum A and have all the unique cell types counted accordingly. basically counting all duplicated and showing bringing up the data onto the side.

Collision 2
Auto , property 2
property 3
Rental 2
property 1

Thannks to whomever can help me.

 

[VoG]

Try a Pivot Table - lots of tutorials here http://www.contextures.com/tiptech.html

 

[DocAElstein]

Re: Macro to sort a list.......would it be possible to sort a list and count how many types are in one list......
Would it be possible to select Colum A and have all the unique cell types counted accordingly. basically counting all duplicated and showing bringing up the data onto the side.....

Hi vasiliskyj,
. Hi I am a VBA Beginner practicing with these sorts of sorting things.
. I have done a macro for you. There are probably better ways to do this. I could maybe have another go in a day or so if no one else gives you something better in the meantime!
. The following code:

<font face=Calibri><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN> <SPAN style="color:#007F00">'Not necerssary, but helps to find errors</SPAN><br><SPAN style="color:#00007F">Sub</SPAN> SimpleSort()<br><SPAN style="color:#00007F">Dim</SPAN> LastDataRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">'Maximum Row set to 255</SPAN><br><SPAN style="color:#00007F">Let</SPAN> LastDataRow = Cells(Rows.Count, 1).End(xlUp).Row <SPAN style="color:#007F00">'Get last used Row</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> DataRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>, NewDataRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">' Row Numbers</SPAN><br>Rows(1).Insert <SPAN style="color:#007F00">' This and the next line are a bodge to get it to work!</SPAN><br>Cells(1, 1).Value = "tempory Row bodge to get it to work!" <SPAN style="color:#007F00">'I Do not know why yet!!</SPAN><br>Range("A1:A10").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("B1"), Unique:=<SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#007F00">'Copy Only Unique values to Column 2</SPAN><br>Rows(1).Delete <SPAN style="color:#007F00">'Delete additional first row which was needed for the bodge but is not needed finally</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> LastNewDataRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN><br><SPAN style="color:#00007F">Let</SPAN> LastNewDataRow = Cells(Rows.Count, 2).End(xlUp).Row <SPAN style="color:#007F00">'Get Last Row of new sorted data</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> MatchCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">' Number of matches in name</SPAN><br>  <SPAN style="color:#00007F">For</SPAN> NewDataRow = 1 <SPAN style="color:#00007F">To</SPAN> LastNewDataRow <SPAN style="color:#007F00">' For each new Data Row.....</SPAN><br>  <SPAN style="color:#00007F">Let</SPAN> MatchCount = 0 <SPAN style="color:#007F00">' Initially no matches</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> DataRow = 1 <SPAN style="color:#00007F">To</SPAN> LastDataRow - 1 <SPAN style="color:#007F00">'Go through every data Row</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> Cells(DataRow, 1).Value = Cells(NewDataRow, 2).Value <SPAN style="color:#00007F">Then</SPAN> MatchCount = MatchCount + 1 <SPAN style="color:#007F00">'Look for a name match. If found, increase count for match</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> DataRow <SPAN style="color:#007F00">' go on to next data Row</SPAN><br>  Cells(NewDataRow, 2).Value = Cells(NewDataRow, 2).Value & " " & MatchCount <SPAN style="color:#007F00">'After going through every Row, Add the Match count after the name and in the same cell by concatenating</SPAN><br>  <SPAN style="color:#00007F">Next</SPAN> NewDataRow <SPAN style="color:#007F00">'Go on to next New data row</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN> <SPAN style="color:#007F00">'SimpleSort()</SPAN></FONT>



. Changes your data from this:

Book1
	A	B
1	collision
2	collision
3	auto, property
4	auto property
5	property
6	property
7	property
8	rental
9	rental
SimpleSort

. To this.

Book1
	A	B
1	collision	collision 2
2	collision	auto, property 1
3	auto, property	auto property 1
4	auto property	property 3
5	property	rental 2
6	property
7	property
8	rental
9	rental
SimpleSort

. Here is the file with your data and the macro in it. The macro is called SimpleSort

FileSnack | Easy file sharing

. See how you get on with it and get back if you need any more help.

Alan Elston

P.s..
. 1 You can put up to 255 Rows of data in. This code can easily be modified to allow you to have lots more rows.
. 2 With your current data you have an auto, property and an auto property. The Code could probably be modified to take similar things as being the same, but I am not quite sure yet how to do that! (-Maybe in a day or two!!)