• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 123
  • Last Modified:

Unique values

Applying AdvancedFilter Unique:=True to a simple column (A) values of, say, 1,1,1,1,1,2,3,4,5,6,7,7

will result in :

1,1,2,3,4,5,6,7

and NOT the required 1,2,3,4,5,6,7

Why?  Thanks, k.
0
krakatoa
Asked:
krakatoa
  • 5
  • 4
2 Solutions
 
Saqib Husain, SyedEngineerCommented:
Because the first 1 is considered as a field name. Change the first 1 to a meaningful name like "Numbers" and then try it.
0
 
krakatoaAuthor Commented:
Your comment of course works. But I find it impossible to see that can be reasonable behaviour by Excel. I have no need nor opportunity of identifying a column header - field name -  in my data. Cell A1 should be treated as any other cell in the range. Surely there must be a fully unique-compliant way of doing this?

What's more, the field header will end up in any copying of the data that I do, and I do not want that at all.

I'd go so far as to say that IMO it's one of the most ridiculous and application-defeating, lame functions I've yet seen in Excel. The fact that the Filter function has no optional header exclusion parameter is beyond belief.
0
 
Dan CraciunIT ConsultantCommented:
You can do this in VBA and you will get the results you want:
ActiveSheet.Range("$A$1:$A$20").RemoveDuplicates Columns:=1, Header:=xlNo

Open in new window

Or just go to Data->Remove Duplicates and leave the "My data has headers" box unchecked.

HTH,
Dan
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
krakatoaAuthor Commented:
Dan - thanks but

438 Error - Object doesn't support this property or method.
0
 
Dan CraciunIT ConsultantCommented:
It does on Excel 2013.
0
 
krakatoaAuthor Commented:
I have Excel 2000. :(
0
 
Dan CraciunIT ConsultantCommented:
Well, someone at Microsoft will be happy about the next question: maybe it's time to upgrade? :)
0
 
krakatoaAuthor Commented:
Yeah . . .  s/one at MS is always happy I'd reckon. ;)

The fact is I'd already written my own (very long, but effective) de-dupe routine, but as it's in a sub with a load of other stuff, I wanted to make a new sub - which also will need a de-dupe routoine too -, less busy by using this AdvancedFilter option. Now that I see I can't, I'll have to use my own again. But even *that* leaves a bitter taste, as, like you said, it's all redundant if one has 2013.
0
 
Dan CraciunIT ConsultantCommented:
This says that the RemoveDuplicates method is available since Excel 2007.
0
 
krakatoaAuthor Commented:
Yup. But as I said, I have Excel 2000.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now