Filter displayed on another sheet

L

Legacy 5596

Guest
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)
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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.
 
L

Legacy 5596

Guest
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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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.
Code:
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
 
L

Legacy 5596

Guest

ADVERTISEMENT

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?
 
L

Legacy 5596

Guest
filter

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

Forum statistics

Threads
1,141,678
Messages
5,707,780
Members
421,527
Latest member
Tamiwsw

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