unique records by advanced filter

[Stefi]

Hi All,

I'd like to make a simple filter for unique records copied to another
location.

The layout is this: two columns, A:B, 14 rows including a header. Unique
filter is to be made by column A, but I want to copy both columns to a new
location.

When in the advanced filter dialog
List range: $A$1:$A$14
criteria range: $A$1:$B$14
then in-place filtering is correct, but I can't in any way achieve the
filtered range copied to another location, say C1.

Any ideas?

Another question is that why List range has to be $A$1:$A$14 (the volumn by
which uniqueness is checked)? For me calling this range criteria range should
be more logical, and range $A$1:$B$14 should be called List range (cells I
want to list in another place).

Maybe I misunderstand something not being a native English speaker, please
explain me the correct usage of filtering unique records!

Thanks,
Stefi

 

[Sean Timmons]

Should work if you do as you assumed you should.

List Range should be $A$1:$B$14. Criteria range should be $A$1:$A$14 if
column A has your duplicate records. Otherwise, make it $B$1:$B$14

Assuming you are selecting Unique records and you are attempting the paste
to another place in the same worksheet.

 

[Stefi]

Thanks Sean, but with your settings nothing happens. LIST range has to be
$A$1:$A$14, and CRITERIA range has to be $A$1:$B$14 to make a correct
filtering in-place (I also thought it should be the opposite way like you
wrote, that's why I posted 2nd part of my question).

What is a bigger problem, copying to another location doesn't work even with
the above settings. Why?

Regards,
Stefi


„Sean Timmons†ezt írta:

 

[Gord Dibben]

Stefi

Leave the criteria range blank and just select the single column for List
Range.

Copy to set for C1 should be OK

For more on this and a video see Debra Dalgleish's site.

http://www.contextures.on.ca/xladvfilter01.html#FilterUR


Gord Dibben MS Excel MVP

 

[Stefi]

Thanks Gord, I followed your instruction and found that (provided $A$1:$A$14
is entered in List range)
1. leaving the criteria range blank or filling it with $A$1:$B$14 gives the
same result when in-place filtering is made (in other words the content of
criteria range was neutral),
2. Entering C1 in Copy to box resulted in copying filtered cells only from
column A, I could in no way achieve to copy the filtered cells from column B.

There must be something wrong with this feature or I still misunderstand
something!

Regards,
Stefi


„Gord Dibben†ezt írta:

 

[Gord Dibben]

Sorry, I misunderstood.

If you want to copy both column A and B to another location based on unique
records in column B

List Range..............A1:B14

Criteria Range.............B1:B14.......this is range with the uniques

Copy to range...........C1


Gord

 

[Stefi]

Hi Gord,

Thank you for dealing with my problem, but doing exactly what you wrote
resulted in both columns having been copied but without filtering out the
redundant rows (the entire range A1:B14 has been copied).

Stefi


„Gord Dibben†ezt írta:

 

[Dave Peterson]

Don't use the criteria range (leave it empty).
Do the advanced filter against a single column A1:a14
Make sure unique records is checked.

After the filtering is done, select both columns.
(F5, ctrl-g, edit|goto)
Special|visible cells only

Copy those visible cells (now including both columns)

paste into the new location

 

[Stefi]

Thanks, Dave, I did that, it worked, but it shows that Copying unique records
to a new location feature doesn't work in Excel2003. I wonder has it been
fixed in Excel2007.

Stefi


„Dave Peterson†ezt írta:

 

[Dave Peterson]

Since you want to include more columns that you're filtering on (a single column
for uniqueness), you don't want to copy to another location using that dialog.

You have to use a multiple step process.

On the other hand, if you wanted to copy the entire range (single column or
multiple columns to determine uniqueness), you can start the dialog on the new
sheet.

Then go back to the real data when you're entering addresses into that dialog.

Seems kind of backward, but it works fine.

I don't know if xl2007 changed this "feature".

 

[Stefi]

Well, I can accept that this task can be done only via a multiple step
process, my problem really was that Excel Help didn't stated this clearly. In
my opinion the Help and the logic of Advanced filter dialog box suggest (or
at least allows to draw this conclusion) that this feature should work in the
way I supposed.
Anyway, the question is settled, I'll use the multiple step process, though
I'll still consider it a workaround.
Thanks,
Stefi


„Dave Peterson†ezt írta: