Displaying Only Rows that Meet Certain Criteria, no Spaces

[sunday_storyteller]

If I have the following data:
<table cellpadding=6><tr><td>Inter</td><td>Zone</td><td>LOCATION</td><td> REPEAT</td>
<tr><td>141</td><td>14</td><td>ABCDEF1141</td><td>1</td>
<tr><td>140</td><td>14</td><td>ASDFKJG1140</td><td>1</td>
<tr><td>025</td><td>02</td><td>ASDFLKJGE1025</td><td>0</td>
<tr><td>142</td><td>14</td><td>LKJMGJHS3142</td><td>1</td>
<tr><td>151</td><td>15</td><td>IMVHHHFW1151</td><td>0</td><tr><td>142</td><td>14</td><td>TESTDATAW3142</td><td>2</td><tr><td>529</td><td>52 </td><td>LKGNEE2529</td><td>2</tr></table>

And I would like to display this data in another sheet, using these guidelines:
Only display rows that have a "2" in the Repeat Column.
Sort the results in order of Zone (ascending).

Is there a way to do that? This information is populated by formulas, so I need any system to look at the values in the cells as if they are values, not formulas, when selecting and sorting.

I've made a work-around, where I paste the data as values-only into an intermediate worksheet, then sort that, then use the values-only data to fill in my other worksheet, but that is alot of work, and I need to be able to do this weekly (maybe multiple times).

Thanks!

Calinda

P.S. I can't use the html download to post my workbook, or I would. I don't have permissions to install new things on the computer. If someone would like to see an example, I can send it to you...but that would not help anyone else on the board who has the same problem. Thanks!

 

[iggydarsa]

if you are confortable with macros you can use the following code, which transfers datarow where Repeat column (column D) is 2 in "Sheet1" and transfers it to "Sheet2" and sorts it:

Sub Transfer_and_Sort()
Dim LastRow1 As Long: LastRow1 = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Dim LastRow2 As Long: LastRow2 = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
If LastRow2 = 1 And Worksheets("Sheet2").Range("A1").Value = "" Then LastRow2 = 0

'Transfer
For i = 2 To LastRow1
    If Worksheets("Sheet1").Range("D" & i).Value = 2 Then
        Worksheets("Sheet1").Range("A" & i & ":D" & i).Copy
        LastRow2 = LastRow2 + 1
        Worksheets("Sheet2").Select
        Range("A" & LastRow2).Select
        ActiveSheet.Paste

    End If
Next i

'Sort
Worksheets("Sheet2").Range("A1:D" & LastRow2).sort Key1:=Range("B1"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End Sub