Help, inconsistent results on Filter - Unique, to new location.
 

Hi,

I’m trying to resolve a list of names into a new list of ‘unique’
names.

On sheet_1 I have a list of names in a column, some appear several
times.

I copy this column from sheet_1, to sheet_2 column “A”.

1. I then select - Data - Filter - Advanced Filter - (Copy to
another location) - (Unique Records Only).
2. For the “List Range” I select column “A” of sheet_2 which resolves
to “$A:$A”.
3. For the “Copy To:” I select column “B” of sheet_2 which resolve to
“$B:$B”.

Problem:
1. Sometimes the above just does not work, resulting in no copy to:
column “B”, yet sometimes it does (using the exact same data).
2. When I record the process in a macro, it never works ?

Any solutions ?


--
jay
------------------------------------------------------------------------
jay's Profile: http://www.excelforum.com/member.php...fo&userid=2377
View this thread: http://www.excelforum.com/showthread...hreadid=515000

Help, inconsistent results on Filter - Unique, to new location.
 

Hi

2. When I record the process in a macro, it never works ?

If you run this do you have problems then

Sub test()
Sheets("Sheet2").Columns(1).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Sheets("Sheet2").Range("B1"), Unique:=True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"jay" wrote in message ...

Hi,

I'm trying to resolve a list of names into a new list of 'unique'
names.

On sheet_1 I have a list of names in a column, some appear several
times.

I copy this column from sheet_1, to sheet_2 column "A".

1. I then select - Data - Filter - Advanced Filter - (Copy to
another location) - (Unique Records Only).
2. For the "List Range" I select column "A" of sheet_2 which resolves
to "$A:$A".
3. For the "Copy To:" I select column "B" of sheet_2 which resolve to
"$B:$B".

Problem:
1. Sometimes the above just does not work, resulting in no copy to:
column "B", yet sometimes it does (using the exact same data).
2. When I record the process in a macro, it never works ?

Any solutions ?


--
jay
------------------------------------------------------------------------
jay's Profile: http://www.excelforum.com/member.php...fo&userid=2377
View this thread: http://www.excelforum.com/showthread...hreadid=515000

Help, inconsistent results on Filter - Unique, to new location.
 

try using the exact extent of your data with a header in A1 as your data
range. Use a single cell as the destination.

Dim rng as Range
With worksheets("sheet_2")
set rng = .range(.cells(1,1), .cells(rows.count,1).End(xlup))
rng.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=.Range("B1"), Unique:=True
End with

--
Regards,
Tom Ogilvy


"jay" wrote in message
...

Hi,

I'm trying to resolve a list of names into a new list of 'unique'
names.

On sheet_1 I have a list of names in a column, some appear several
times.

I copy this column from sheet_1, to sheet_2 column "A".

1. I then select - Data - Filter - Advanced Filter - (Copy to
another location) - (Unique Records Only).
2. For the "List Range" I select column "A" of sheet_2 which resolves
to "$A:$A".
3. For the "Copy To:" I select column "B" of sheet_2 which resolve to
"$B:$B".

Problem:
1. Sometimes the above just does not work, resulting in no copy to:
column "B", yet sometimes it does (using the exact same data).
2. When I record the process in a macro, it never works ?

Any solutions ?


--
jay
------------------------------------------------------------------------
jay's Profile:
http://www.excelforum.com/member.php...fo&userid=2377
View this thread: http://www.excelforum.com/showthread...hreadid=515000

Help, inconsistent results on Filter - Unique, to new location.
 

Why do you copy column A to sheet2 column A? Can't you just filter uniques
directly to A1 of sheet2 from column A of sheet1?

Manually, insert sheet2
select A1
then do the data|filter|advanced filter
and point at the ranges you want (I'd just use A1 of sheet2 for the destination
range).

I recorded a macro when I did it manually and got this:

Sheets("Sheet1").Columns("A:A").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("A1"), Unique:=True

I'd be more specific and qualify that copytorange:

Sheets("Sheet1").Columns("A:A").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=sheets("sheet2")Range("A1"), Unique:=True



jay wrote:

Hi,

I’m trying to resolve a list of names into a new list of ‘unique’
names.

On sheet_1 I have a list of names in a column, some appear several
times.

I copy this column from sheet_1, to sheet_2 column “A”.

1. I then select - Data - Filter - Advanced Filter - (Copy to
another location) - (Unique Records Only).
2. For the “List Range” I select column “A” of sheet_2 which resolves
to “$A:$A”.
3. For the “Copy To:” I select column “B” of sheet_2 which resolve to
“$B:$B”.

Problem:
1. Sometimes the above just does not work, resulting in no copy to:
column “B”, yet sometimes it does (using the exact same data).
2. When I record the process in a macro, it never works ?

Any solutions ?

--
jay
------------------------------------------------------------------------
jay's Profile: http://www.excelforum.com/member.php...fo&userid=2377
View this thread: http://www.excelforum.com/showthread...hreadid=515000

--

Dave Peterson