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 | |
|---|
|
|---|
. 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 | |
|---|
|
|---|
. 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!!)
