SORT function - by lowest to higher duplicate

[bllanko]

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 11">****** name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CTEMP%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Font Definitions */ @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} span.EmailStyle15 {mso-style-typeersonal; mso-style-noshow:yes; mso-ansi-font-size:10.0pt; mso-bidi-font-size:10.0pt; font-family:Arial; mso-ascii-font-family:Arial; mso-hansi-font-family:Arial; mso-bidi-font-family:Arial; color:windowtext;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Hello,<o></o>
<o> </o>
I have around 800 line items in column “A” that I need to sort out based on how many of them are duplicates or more a like. Example if in column A I have mixed data than on either in same colum (“A”) or column “B” to be sorted by lowest to higher duplicate.<o></o>
<o> </o>
<o> </o>
Example below (note: colors coded are for explanation only):<o></o>
Mixed data To be sort like below:<o></o>
<table class="MsoNormalTable" style="width: 227.6pt; border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="303"> <tbody><tr style="height: 15pt;"> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="152" nowrap="nowrap"> Column A<o></o>
</td> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="152" nowrap="nowrap"> Column B<o></o>
</td> </tr> <tr style="height: 15pt;"> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="152" nowrap="nowrap"> A26329159<o></o>
</td> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="152" nowrap="nowrap"> A25221392<o></o>
</td> </tr> <tr style="height: 15pt;"> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="152" nowrap="nowrap"> A25221392<o></o>
</td> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="152" nowrap="nowrap"> A25221392<o></o>
</td> </tr> <tr style="height: 15pt;"> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="top" width="152" nowrap="nowrap"> A26329159
</td> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="152" nowrap="nowrap"> A25221392<o></o>
</td> </tr> <tr style="height: 15pt;"> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="top" width="152" nowrap="nowrap"> A26329159
</td> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="152" nowrap="nowrap"> A25658999<o></o>
</td> </tr> <tr style="height: 15pt;"> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="152" nowrap="nowrap"> A25658999<o></o>
</td> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="152" nowrap="nowrap"> A25658999<o></o>
</td> </tr> <tr style="height: 15pt;"> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="152" nowrap="nowrap"> A26329159<o></o>
</td> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="152" nowrap="nowrap"> A25658999<o></o>
</td> </tr> <tr style="height: 15pt;"> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="152" nowrap="nowrap"> A25221392<o></o>
</td> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="152" nowrap="nowrap"> A25658999<o></o>
</td> </tr> <tr style="height: 15pt;"> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="top" width="152" nowrap="nowrap"> A25658999
</td> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="152" nowrap="nowrap"> A25658999<o></o>
</td> </tr> <tr style="height: 15pt;"> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="top" width="152" nowrap="nowrap"> A25658999
</td> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="152" nowrap="nowrap"> A26329159<o></o>
</td> </tr> <tr style="height: 15pt;"> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="152" nowrap="nowrap"> A26329159<o></o>
</td> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="152" nowrap="nowrap"> A26329159<o></o>
</td> </tr> <tr style="height: 15pt;"> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="top" width="152" nowrap="nowrap"> A25658999
</td> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="152" nowrap="nowrap"> A26329159<o></o>
</td> </tr> <tr style="height: 15pt;"> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="top" width="152" nowrap="nowrap"> A25658999
</td> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="152" nowrap="nowrap"> A26329159<o></o>
</td> </tr> <tr style="height: 15pt;"> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="152" nowrap="nowrap"> A26329159<o></o>
</td> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="152" nowrap="nowrap"> A26329159<o></o>
</td> </tr> <tr style="height: 15pt;"> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="152" nowrap="nowrap"> A25221392<o></o>
</td> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="152" nowrap="nowrap"> A26329159<o></o>
</td> </tr> <tr style="height: 15pt;"> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="152" nowrap="nowrap"> A26329159<o></o>
</td> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="152" nowrap="nowrap"> A26329159<o></o>
</td> </tr> <tr style="height: 15pt;"> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="152" nowrap="nowrap"> A25658999<o></o>
</td> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="152" nowrap="nowrap"> A26329159<o></o>
</td> </tr> <tr style="height: 15pt;"> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="152" nowrap="nowrap"> A26329159<o></o>
</td> <td style="width: 113.8pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="152" nowrap="nowrap"> A26329159<o></o>
</td> </tr> </tbody></table>

 

[p45cal]

In cell B2 have this formula (assumed headers in row 1):
=COUNTIF($A$2:$A$800,A2)
and copydown.
Sort primarily on column B (ascending), secondarily on column A (ascending).

Excel Workbook
	A	B
1	Head1	Head2
2	A25221392	3
3	A25221392	3
4	A25221392	3
5	A25658999	6
6	A25658999	6
7	A25658999	6
8	A25658999	6
9	A25658999	6
10	A25658999	6
11	A26329159	8
12	A26329159	8
13	A26329159	8
14	A26329159	8
15	A26329159	8
16	A26329159	8
17	A26329159	8
18	A26329159	8
19
20
21
22	Row Labels	Count of Head1
23	A25221392	3
24	A25658999	6
25	A26329159	8
Sheet1

Alternatively create a pivot table, shown in A22:B25 above.

 

[bllanko]

GREAT, thanks a lot.