Formular to sort a list of data

[Clanangus]

Hi

This is my first question posted on the forum. I've been reading it for ages and it has helped me out greatly just reading other peoples posts but now I have come across a problem I can't seem to find an answer to. Any help would be appreciated.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
What I am trying to do is to take a list of results similar to this...
<o></o>
<o></o>
ID - Depth - Value<o></o>
BH1 - 1 - 10<o></o>
BH1 - 1.5 - 9<o></o>
BH1 - 2 - 14<o></o>
BH2 - 1.5 - 12<o></o>
BH2 - 2 - 12<o></o>
BH3 - 2 - 15<o></o>
BH3 - 2.5 - 14<o></o>
<o></o>
And then produce separate lists somewhere in the same sheet that sort this data by ID producing separate lists like this....
.<o></o>
<o></o>
ID - Depth - Value <o></o>
BH1 - 1 - 10<o></o>
BH1 - 1.5 - 9<o></o>
BH1 - 2 - 14
.<o></o>
<o></o>
ID - Depth - Value<o></o>
BH2 - 1.5 - 12<o></o>
BH2 - 2 - 12
.<o></o>
<o></o>
ID - Depth - Value<o></o>
BH3 - 2 - 15<o></o>
BH3 - 2.5 - 14.
<o></o>
<o></o>
<o></o>
To complicate matters I need it to be a live formula so that when a new entry is put in the results list, it automatically appears correctly sorted. <o></o>
<o></o>
Any help and advice would be very kindly received<o></o>

 

[venkat1926]

try this macro "test" (mcro "undo" is to undo the result of the macro test)
the result is in sheet 2

Sub test()
Dim ra As Range, rfull As Range, rfilt As Range, run As Range, cun As Range, j As Integer
j = 0
Worksheets("sheet1").Activate
Set ra = Range(Range("A1"), Range("A1").End(xlDown))
Set rfull = Range("A1").CurrentRegion
Set run = Range("a1").End(xlDown).Offset(5, 0)
ra.AdvancedFilter action:=xlFilterCopy, copytorange:=run, unique:=True
Set run = Range(run.Offset(1, 0), run.End(xlDown))
For Each cun In run
rfull.AutoFilter field:=1, Criteria1:=cun
Set rfilt = ActiveSheet.AutoFilter.Range
rfilt.Copy Worksheets("sheet2").Range("a1").Offset(0, j)
j = j + 5
rfull.AutoFilter
Next cun

End Sub

Sub undo()
Worksheets("sheet2").Cells.Clear
End Sub