Displaying Only Rows that Meet Certain Criteria, no Spaces

sunday_storyteller

Board Regular
Joined
Jun 22, 2007
Messages
51
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!
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,665
Office Version
  1. 2019
Platform
  1. Windows
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:

Code:
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
 

Forum statistics

Threads
1,181,658
Messages
5,931,270
Members
436,785
Latest member
KingGideon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top