Advanced Filter

[austenr]

Is it possible to extract the unique records from 2 lists in Sheet1 Columns A & B and write them to those columns in Sheet2 using Advanced Filter. Or VBA if there is a simple solution. Thanks

[Bob Phillips]

The big problem with Advanced filter is that it needs to filter to the same sheet.

So i have filtered onto the same sheet, then moved it

Sub FilterData()
Dim rng As Range
Dim sh As Worksheet
Worksheets("Sheet1").Activate
Set rng = Range("F1:G12")
Set sh = ActiveSheet
With rng
.Offset(0, .Columns.Count).Resize(, .Columns.Count).EntireColumn.Insert
.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=.Offset(0, .Columns.Count), _
Unique:=True
.Offset(0, .Columns.Count).Resize(, .Columns.Count).Cut _
Destination:=Worksheets("Sheet2").Range("A1")
.Offset(0, .Columns.Count).Resize(, .Columns.Count).Delete Shift:=xlToLeft
End With
End Sub

[Zack Barresse]

The big problem with Advanced filter is that it needs to filter to the same sheet...

No it doesn't.

[austenr]

I am confused...Which is right? Not that I am not greatful to both of you but what is correct and why?

[Zack Barresse]

Well, it's not really about right and wrong, it's about efficient and inefficient. xld's way of doing it should work - which doesn't make it wrong. But you can cut out the whole copy part which makes it slightly less efficient. Here are a couple of examples ...

Option Explicit

Sub AFoneCol()
    Dim rngFilter As Range, lngRow As Long
    lngRow = Sheets("Sheet1").Range("A65536").End(xlUp).Row
    Set rngFilter = Sheets("Sheet1").Range("A1:A" & lngRow)
    rngFilter.AdvancedFilter _
        Action:=xlFilterCopy, _
        CopyToRange:=Sheets("Sheet2").Range("A1"), _
        Unique:=True
End Sub

Sub AFtwoCols()
    Dim rngFilter As Range, lngRow As Long
    lngRow = Sheets("Sheet1").Range("A65536").End(xlUp).Row
    Set rngFilter = Sheets("Sheet1").Range("A1:B" & lngRow)
    Range("A1:B31").AdvancedFilter _
        Action:=xlFilterCopy, _
        CopyToRange:=Sheets("Sheet2").Range("A1"), _
        Unique:=True
End Sub

[Bob Phillips]

Zack is right, it is the activesheet, not the same sheet, so my move could be avoided by working from the target sheet not the source sheet.

[Zack Barresse]

.. working from the target sheet not the source sheet.

And that's the key right there. Kind of a PITA by MS imho.

[austenr]

I see where you are going. These examples can get me going. Thanks

[austenr]

A question after running the second macro Zack, on the second sheet, i get the number 13. Not quite sure where that is coming from? Sure it is a simple explaination.

[Zack Barresse]

Not sure what you mean. Is that part of your data?

[austenr]

No. The number 13 does not appear in the data. Let me attach a sample.

[austenr]

OOPS...

[Zack Barresse]

Can't tell ya. Works for me. Although you altered the range to something a little wierd. Go over the ranges in these examples ...

Option Explicit
 
Sub AFoneCol()
    Call clearMe
    Dim rngFilter As Range, lngRow As Long
    lngRow = Sheets("Sheet1").Range("A65536").End(xlUp).Row
    Set rngFilter = Sheets("Sheet1").Range("A1:A" & lngRow)
    rngFilter.AdvancedFilter _
        Action:=xlFilterCopy, _
        CopyToRange:=Sheets("Sheet2").Range("A1"), _
        Unique:=True
End Sub
 
Sub AFtwoCols()
    Call clearMe
    Dim rngFilter As Range, lngRow As Long
    lngRow = Sheets("Sheet1").Range("A65536").End(xlUp).Row
    Set rngFilter = Sheets("Sheet1").Range("A1:B" & lngRow)
    rngFilter.AdvancedFilter _
        Action:=xlFilterCopy, _
        CopyToRange:=Sheets("Sheet2").Range("A1"), _
        Unique:=True
End Sub

Sub clearMe()
    Sheets("Sheet2").Cells.Clear
End Sub

Routine clearMe is only for testing purposes.

And btw, you should add headers/labels to your data; not sure if that is only a test file or what.

[austenr]

ok thanks

[TonyJollans]

The big problem with Advanced filter is that it needs to filter to the same sheet.

No it doesn't.

Zack is right. Advanced Filter can go from any worksheet to any worksheet.

Zack is right, it is the activesheet, not the same sheet, so my move could be avoided by working from the target sheet not the source sheet.

This constraint is imposed when using the dialog but not when using code. As all the examples posted here are in code, there are no constraints.

If you want to do it without code go to Sheet2 and invoke the dialog (Data > Filter > Advanced Filter) - select your source range on Sheet1, select your target range on Sheet2 (A1?), check the appropriate boxes/buttons and press OK.