Here's an approach to try (it might seem a bit long, but it's really pretty
easy):
Example Assumptions:
Sheet1 contains your data in cells A1:Z100
Sheet2 is where you want the extracted data to be displayed
(of course, you'll need to adjust the range references to suit your situation)
Using Sheet2:
Insert>Name>Define
Names in workbook: Sheet2!rngDest
Refers to: =Sheet2!$A$1:$Z$1
Next...still on Sheet2:
Insert>Name>Define
Names in workbook: Sheet2!rngSource
Refers to: =Sheet1!$A$1:$Z$100
(Notice: you are on Sheet2, and creating a Sheet2 level range name, but
the referenced range is on Sheet1)
The reason: An advanced filter cannot SEND data to another sheet, but
it can PULL data from another sheet.
Now...set up the Advanced Data Filter:
Data>Filter>Advanced Data Filter
Select: Copy to another location
Select: Unique Values
List Range: (press F3 and select rngSource)
Criteria Range: (leave this blank)
Copy To: (press the [F3] key and select rngDest)
Click [OK]
Note: if you want to run that Advanced Data Filter repeatedly,
you'll need to re-select rngSource each time
.....OR...if you're feeling a bit ambitious...
You can build a simple macro to automatically re-run the filter:
Press [Alt]+[F11] to open the VBA editor
Right click on the VBA Project folder for your workbook
Select: Insert>Module
Then, copy/paste this code into that module:
'---Start of Code-------
Option Explicit
Sub PullMatchingData()
Range("Sheet2!rngSource").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("Sheet2!rngDest"), _
Unique:=True
End Sub
'---Start of Code-------
To run the code:
Tools>Macro>Macros (or [Alt]+[F8])
Select and run: PullMatchingData
Does that help?
***********
Regards,
Ron
