Filter displayed on another sheet

[Legacy 5596]

I have sheet#1 with a list, for example:

col A col B col C
==== ==== ====
Company Name phone numbers

Some of the entries in col A will be duplicates since a company may have more than one entry. I want to create a macro attached to a dropdown box that only shows one instance of each company, but will output on sheet#2 like the typical "filter" function. (Dropdown box should be on sheet#2 as well)

Thanks for any help

P.S. (The reason I want to do it this way rather than have people simply filter on sheet#1 is sheet#1 is a large database I don't want the user viewing directly)

 

[Datsmart]

Look into Advanced Filter. It allows you to "Filter to Location". So you can setup the filter criteria on one page and then have the filtered data display on a third page.

 

[Legacy 5596]

Advanced Filter

Look into Advanced Filter. It allows you to "Filter to Location". So you can setup the filter criteria on one page and then have the filtered data display on a third page.

Ok let me see if I can figure that out -- thanks

 

[Datsmart]

Good example of Advanced Filter steps.
http://www.contextures.com/xladvfilter01.html

 

[Legacy 5596]

Advanced filters

Good example of Advanced Filter steps.
http://www.contextures.com/xladvfilter01.html

OK that was helpful except that it doesn't seem to update the sheet#2 if I change the criteria (which would be the dropdown list) located in a cell on sheet#2

 

[Datsmart]

Here is some code I use to change the final data sheet by the fields I have on the Cirteria page.
I use a macro button to activate the code after making the field changes.
Change code to suit your scenario.

Sub FilterToAnotherSheet()
    Dim FilterRange As Range, Criteria As Range, TargetRange As Range
    Dim RangeCritria As Range, RangeData As Range

'This Macro assumes that the following three Worksheets exist:
'   Sheets.("Data")         'Contains main table of data
'   Sheets.("Criteria")     'Contains small Criteria table begining at cell A1.
'                           'must include header of main table and desired criteria.
'                           'input text of interest in one or more fields.
'   Sheets.("AdvancedData") 'Receives filtered data per Criteria

' Clear old data first
    Sheets("AdvancedData").Range("A2:K12").Clear

' Delete old named ranges
    On Error Resume Next
    ActiveWorkbook.Names("RangeCritria").Delete
    ActiveWorkbook.Names("RangeData").Delete

' Create new named ranges and Set variables
    Sheets("Criteria").Range("A1").CurrentRegion.Name = "RangeCriteria"  'Names the range
    Sheets("Data").Range("A4").CurrentRegion.Name = "RangeData"  'Names the range
    Set RangeCriteria = Sheets("Criteria").Range("A1").CurrentRegion
    Set FilterRange = Sheets("Data").Range("RangeData")
    Set Criteria = RangeCriteria
' Assign position to export filtered data to
    Set TargetRange = Sheets("AdvancedData").Range("A2")
    
' Run Advanced Filter
    FilterRange.AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=TargetRange, CriteriaRange:=Criteria

' Goto updated sheet
    Sheets("AdvancedData").Select
End Sub

 

[Legacy 5596]

ugh -- getting too complex for me -- lets go another route.
Do you have a formula to remove duplicates?
For instance, as col A have 20 names & some of the names are duplicates. Say I want col B to display the corresponding row in col A UNLESS I've already displayed it once in col B. Any code I could use in col B to do that?

 

[Datsmart]

Take a look at this post.
http://www.mrexcel.com/board2/viewtopic.php?t=98896&postdays=0&postorder=asc&&start=10
It contains several suggestions, both using code and formulae.
Working with duplicates is always a challenge.

 

[Legacy 5596]

filter

Thanks for your help John, though I couldn't use that for what I'm doing you did teach me something