Advanced filter unique values in Col A, based on 2 criteria in Col B !

N3ilG

New Member
Joined
Jun 7, 2015
Messages
11
Hi - I've scanned through all the relative posts and iterations, and have probably started to confuse myself!

I can get the advanced filter to filter my data on column N to two criteria and copy all rows to a separate worksheet, but I only want to copy rows where both criteria are met.

In other words Col A has an order number, and Col N a schedule of rates code applied for valuation purposes - so what I want to do is highlight those orders where a combination of both rates have been applied.

I hope that makes sense, and would be grateful if any one can signpost me to a solution. I often scour the threads for a solution but am mindful that I often create fairly crude <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">vba</acronym>.

Thanks - Neil
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

N3ilG

New Member
Joined
Jun 7, 2015
Messages
11
Okay so my earlier post wasn't well constructed and lazy of me ! Using the following code, I can filter out a record (with numerous occurrences, 1 per code applied to it - code being a alpha numeric code relating to completed works).
So a works order number in Col A (134567/1) might appear twice with 2 separate alpha numeric on seperate lines (HS898001, HS357890).

What I want to do is filter the data using set criteria for the codes used, and then look for occurences where 2 particular codes have been used together on the same job. Almost like filtering the works order number by instances of more than 1.

Code:
Sub searchtext1()
    
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
    
    Set ws1 = ThisWorkbook.Worksheets(1)    'assumes raw data is always first sheet
    Set ws2 = ThisWorkbook.Worksheets(2)
    Set ws3 = ThisWorkbook.Worksheets(3)
    
    If ws1.FilterMode Then ws1.ShowAllData
    
    ws3.UsedRange.ClearContents
    'ws2.Activate
    
    ws1.Range("A1").CurrentRegion.AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=ws2.Range("N1", ws2.Range("N" & Rows.Count).End(xlUp)), _
        CopyToRange:=ws3.Range("A1")
        
    If ws1.FilterMode Then ws1.ShowAllData
    
End Sub

Any help is much appreciated !
 

Watch MrExcel Video

Forum statistics

Threads
1,122,481
Messages
5,596,391
Members
414,063
Latest member
N_Bates

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