Duplicate Values returned when using Advanced Filter - Unique Values only

[johnmsharkey]

I have a large list of values. I am using the advanced filter with the unique records only, but I am still getting one record that shows up twice. It shows up twice in the original , so I tried to see if there was any difference between the two entries. The only thing I could tell was the font size was different. I made them the same but it didn't make a difference. I did notice that when I try to change the number format, no matter what I set it as, it always reverts to Category: Special Type: Zip Code Location: Portugal. Not sure why it does this or if it has any affect on the filter, but thought I would throw it out there. Any help would be appreciated. Thanks.

 

[Ron Coderre]

There *must* be something different between the two records.

Try this:
• Select one of the duplicate records.
• Copy/paste it on another sheet
• Select the other duplicate
• Copy/paste it under the other copy

Assuming the records are in rows 1 and 2
• A3: =(A1=A2)
• Copy that formula across as far as you need.
The cells that return FALSE are where you should look.

Does that help?

 

[johnmsharkey]

Well, that returned TRUE, so I'm still not sure what is causing the issue. I did try to retype in the values and then re-filter and that fixed it. But I am still curious as what caused the error in the first place.

 

[AzDayton]

Well, that returned TRUE, so I'm still not sure what is causing the issue. I did try to retype in the values and then re-filter and that fixed it. But I am still curious as what caused the error in the first place.

I found that when you're making a new list from an old, with unique values, putting it into a new location, then if the list you're copying from does not contain a header, Excel will include the first item in the list as the header. This gives the appearance of duplicating values, but it's really just copying what it thinks is the header. For example:
.

Without Header			With Header
(Old)	(New)		(Old)	(New)
AAA	AAA		My List	My List
BBB	BBB		AAA	AAA
AAA	AAA		BBB	BBB
CCC	CCC		AAA	CCC
DDD	DDD		CCC	DDD
BBB			DDD
			BBB

<tbody>
</tbody>

Hope that helps someone with similar puzzlements!

- Dayton - Mon. 12/03/2012 @ 11:12:12

 

[Ritika]

Use a header row in your data

 

[DocAElstein]

I found that when you're making a new list from an old, with unique values, putting it into a new location, then if the list you're copying from does not contain a header, Excel will include the first item in the list as the header. This gives the appearance of duplicating values, but it's really just copying what it thinks is the header. For example:
.

Without Header			With Header
(Old)	(New)		(Old)	(New)
AAA	AAA		My List	My List
BBB	BBB		AAA	AAA
AAA	AAA		BBB	BBB
CCC	CCC		AAA	CCC
DDD	DDD		CCC	DDD
BBB			DDD
			BBB

<tbody>
</tbody>

Hope that helps someone with similar puzzlements!

....It did ( or would have If I had found this thread )

.....Hi,
. As a beginner, I encountered exactly this problem yesterday answering a Thread where a Column of data which had some duplicated values had to be filtered to give just the unique values in a Column. Coincidentally the OP had no headers. If he had of had headings then I may never of hit on this “Problem” – A quick google-site:Mrexcel thing gave me quickly the answer (several times!- here just a few:….

http://www.mrexcel.com/forum/excel-...filter-visual-basic-applications-problem.html

http://www.mrexcel.com/forum/excel-...-creating-unique-records-advanced-filter.html

http://www.mrexcel.com/forum/excel-...blem-advanced-filter-xlfiltercopy-unique.html …..).
(I did not hit on this thread unfortunately – I just saw it by coincidence today!?!)

. So it seems unanimous that The Advanced Filter Method (with argument, Unique) takes the first Row from a Range as a Heading and copies it to the first Row as a Heading of the filtered Range.

. In a real life example with lots of data if you did not know that you may well be fooled into thinking the first row was filtered unique data. –I bet there are out there some data lists in error because of this. 10/10 to the power of Google combined with this Forum to get that gem of Info. 0/10 To Microsoft for a) Not stating that clearly anywhere (I stand to be corrected- I could not find it??). ; b) having that as the default ; and c) that no argument option that allows to disable this and just filter data, which is what is often required (- again I would be happy to be corrected on this) – my “Bodge” was to insert a line with arbitrary headings in , do the actual required filtering, and then delete the unwanted line complete with the headings! – Very unprofessional to say the least.

Alan.
P,s shame there is not a “Auto-Filter-Merge” thing to bring the duplicate MrExcel Threads together!!