sorting of data in excel

[rupashree]

please give me a code to sort data in this way...the numbers changes daily.for example the data is :

<table border="0" cellspacing="0" cols="3" frame="VOID" rules="NONE"> <colgroup><col width="155"><col width="109"><col width="102"></colgroup> <tbody> <tr> <td width="155" align="LEFT" height="17">SPMP906228</td> <td width="109" align="LEFT">SPMP905960</td> <td width="102" align="LEFT">SPMP905960</td> </tr> <tr> <td align="LEFT" height="17">SPMP905960</td> <td align="LEFT">SPMP908349</td> <td align="LEFT">SPMP908349</td> </tr> <tr> <td align="LEFT" height="17">SPMP908349</td> <td align="LEFT">SPMP907370</td> <td align="LEFT">
</td> </tr> <tr> <td align="LEFT" height="17">SPMP907370</td> <td align="LEFT">
</td> <td align="LEFT">
</td> </tr> <tr> <td align="LEFT" height="17">SPMP906327</td> <td align="LEFT">
</td> <td align="LEFT">
</td> </tr> </tbody> </table>
and i need data as mentioned below:

<table border="0" cellspacing="0" cols="3" frame="VOID" rules="NONE"> <colgroup><col width="123"><col width="109"><col width="102"></colgroup> <tbody> <tr> <td width="123" align="LEFT" height="17">SPMP906228</td> <td width="109" align="RIGHT">
</td> <td width="102" align="RIGHT">
</td> </tr> <tr> <td align="LEFT" height="17">SPMP905960</td> <td align="LEFT">SPMP905960</td> <td align="LEFT">SPMP905960</td> </tr> <tr> <td align="LEFT" height="17">SPMP908349</td> <td align="LEFT">SPMP908349</td> <td align="LEFT">SPMP908349</td> </tr> <tr> <td align="LEFT" height="17">SPMP907370</td> <td align="LEFT">SPMP907370</td> <td align="LEFT">
</td> </tr> <tr> <td align="LEFT" height="17">SPMP906327</td> <td align="LEFT">
</td> <td align="LEFT">
</td> </tr> </tbody> </table>
please reply its urgent

 

[venkat1926]

david Mcritchie has a solution for this
provided you are not concerned with the change of sorting

in the sheet data is like this with column headings.

Excel Workbook
	A	B	C
1	hdng1	hdng2	hdng3
2	SPMP906228	SPMP905960	SPMP905960
3	SPMP905960	SPMP908349	SPMP908349
4	SPMP908349	SPMP907370
5	SPMP907370
6	SPMP906327
Sheet1

now run David's macro "pushdown_high"

and see what happens.

DO THIS ON THIS SAMPLE DATA AND THEN ONLY TRY IT ON A COPY OF THE ORIGINAL DATA (KEEPING ORIGINAL DATA SAFE)

the mcros are (restart is to undo the macro pushdown_high")

Sub pushdown_high()
  'David McRitchie,  2005-02-09 rev. 2005-03-04
  Dim r As Long, c As Long, val As String
  Dim too_high As String, LastCol As Long
  LastCol = Cells.SpecialCells(xlLastCell).Column
  too_high = "ZZZZZZZZZZ"
  '--Sort each column starting at row 2 (column has headers)
  For c = 1 To LastCol
    Columns(c).Sort Key1:=Cells(2, c), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
  Next c
  For r = 2 To 5000
    val = too_high
    '-- Find lowest value in a row
    For c = 1 To LastCol
       If Trim(Cells(r, c).Value) <> "" And _
           UCase(Cells(r, c).Value) < UCase(val) Then
         val = Cells(r, c).Value
      End If
    Next c

    '-- if not the lowest value in row push down values in column
    For c = 1 To LastCol
      If Trim(Cells(r, c).Value) <> "" And _
             UCase(Cells(r, c).Value) > UCase(val) Then
         Cells(r, c).Insert Shift:=xlDown
      End If
    Next c
    If val = too_high Then GoTo all_done
  Next r
all_done:
End Sub

Sub pushdown_restart()
   ' Select all data, delete empty cells shifting up
   Cells.Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Delete Shift:=xlUp
End Sub