Adv Filter - Unique Items NOT working

[Joyce]

Hello,

I'm applying an advanced filter, in place, unique values only.

However, it returns doubles. I have used Trim and have also created IF
statements checking if two of the duplicates are the same, cell by cell, and
they are.

Any ideas?

Thanks

 

[Dave Peterson]

If there is only one duplicate--and it's the top row of the range and another
row of the range, then I bet you don't have headers and excel is using that top
piece of data as the header.

Add headers if you don't have them.

If that doesn't help, you may want to share some snippets of data and your exact
steps when you run the advanced filter (along with the version of excel).

ps. If you're filtering a field that consists of numbers/digits, then make sure
that all your fields are the same type--either text or real numbers.

(And changing the number format isn't enough to ensure this!)

If you need help changing this data, explain what you want to do (text to real
numbers or numbers to text).

 

[Joyce]

Hi Dave,

No, all columns have headers and the duplicates are in the middle of the
filtered data. I'm using XL 2003 and have used Advanced Filters for years.
Can't understand why this particular one won't work.

As always, I have copied and pasted the headings that I wish to filter by,
entered the criteria beneath them, for example:

Date Answer Name
=">"&INDEX(QstnaireDate,1,1) Y Jim Smith

I apply the filter, ensuring the data range is correctly selected, the
criteria is the range as above and Unique Records Only is selected.

I filter in place.

Thanks

 

[Dave Peterson]

But you didn't share any of the original data and you didn't share what was
returned--or the value of that firstcell in the qstnairdate named range.

 

[Joyce]

Sorry Dave,

The qstnairedate is a singular cell on another worksheet that contains a date.

An example of the original data would be:

Date Questionnaire Rep
Responded?
January 15, 2009 Questionnaire 3 Mary Jones Y
April 5, 2009 Questionnaire 1 John Smith N
April 3, 2009 Questionnaire 1 Mary Jones Y
March 15, 2009 Questionnaire 2 John Smith Y
April 3, 2009 Questionnaire 1 Mary Jones Y
February 12, 2009 Questionnaire 3 Joe Blow Y
February 15, 2009 Questionnaire 5 Mary Jones N

-------------------

A sample filter is (where QstnaireDate is Jan 1, 2009):

Date Answer Name
=">"&INDEX(QstnaireDate,1,1) Y Mary Jones

-------------------------
Results:

Date Questionnaire Rep
Responded?

January 15, 2009 Questionnaire 3 Mary Jones Y
April 3, 2009 Questionnaire 1 Mary Jones Y
April 3, 2009 Questionnaire 1 Mary Jones Y


I hope that is more clear. I have checked the formatting, have made sure no
extra spaces before/after data, done an IF statement for each testing if one
cell matches other cell and result is True.

Thanks, Dave.

 

[Dave Peterson]

I parsed your data into Sheet1 in A18.

A2:A8 are real dates and the number format is: mmmm d, yyyy
(not simple text)

Then on sheet2, I put this in A11
Date Questionnaire Rep Responded?

Notice that the headers used here matched the headers used on Sheet1. (I'm
wondering why you didn't match the headers in your criteria range???)

I created a named range on Sheet2 (in F1:F7 named qstnairedate) with the date
1/1/2009 and formatted as mm/dd/yyyy (I didn't care).

So my criteria range looked like:
My criteria range looked like:
Date Questionnaire Rep Responded?

39814 Mary Jones Y

Then I applied Data|Filter|advanced filter.
Filter in place
List range: $A$1:$D$8
criteria range: sheet2!$a$1:$d$2
unique records only checked.

Date Questionnaire Rep Responded?
January 15, 2009 Questionnaire 3 Mary Jones Y
April 3, 2009 Questionnaire 1 Mary Jones Y

And I got these two records--but the test data wasn't very good--because each
record (you have to consider all 4 fields) were unique.

So I made the test data look like:

Date Questionnaire Rep Responded?
January 15, 2009 Questionnaire 3 Mary Jones Y
April 5, 2009 Questionnaire 1 John Smith N
January 15, 2009 Questionnaire 3 Mary Jones Y
March 15, 2009 Questionnaire 2 John Smith Y
January 15, 2009 Questionnaire 3 Mary Jones Y
February 12, 2009 Questionnaire 3 Joe Blow Y
February 15, 2009 Questionnaire 5 Mary Jones N

Then I applied the same filter to that range and got these results:
Date Questionnaire Rep Responded?
January 15, 2009 Questionnaire 3 Mary Jones Y

Since I used 3 (of the 4 fields) in my criteria, each field is considered when
determining duplicates. And since there were 3 records that matched each of the
criteria, they were all the same.

====
So my question to you is what do you really want? If you want just the Mary
Jones records, then just use the Rep column in the criteria range (or empty the
other criteria cells).

Date Questionnaire Rep Responded?
Mary Jones

If you wanted Mary Jones records where she's already responded, then you'd use
two columns.

Date Questionnaire Rep Responded?
Mary Jones Y

And remember Unique means that the date criteria can be met, but each different
date will make the record unique.

 

[Joyce]

Hi Dave,

Thanks for your time and effort. I copied and pasted the data into another
workbook and it works.... strange.

Thanks!