copy range of unique value

[Alain R.]

Hi,

i use Excel 2007 and i have a strange behavior with a particular thing
that i do.

quite often i have list of values with several times the same values.
e.g.: 2,3,8,5,4,3,9 from cell A1 to A7

when i use the function:
Data => Sort & Filter => Advanced => Copy to another location, list
range $A$1:$A$7, copy to $D$1 and checked Unique records only.

i correctly get 2,3,8,5,4,9
it means second times that 3 is found, it is removed from the new list.

i have also a good result with only alpha value and only numerical values.

my main problem is when cells have alphanumerical values like
$A$1 = note0450
$A$2 = note0521
$A$3 = note0450
$A$4 = note8427
$A$5 = note0017

i get as result:

$D$1 = note0450
$D$2 = note0521
$D$3 = note0450
$D$4 = note8427
$D$5 = note0017

in this case nothing was removed. it's like i've never checked the
Unique records only check box :-(

or if u try somewhere else on the sheet.
$A$1 = pc05
$A$2 = pc54
$A$3 = pc923
$A$4 = pc43
$A$5 = pc05
$A$6 = pc43
$A$7 = pc98
$A$8 = pc43

i get:
$D$1 = pc05
$D$2 = pc54
$D$3 = pc923
$D$4 = pc43
$D$5 = pc05
$D$6 = pc98

in this case something was removed but not all. for example, pc05 is
another time.

what is the problem ?
if someone wants the file as example i can send him

thanks a for help.

Al.

 

[Bob Umlas]

It's because the first item is treated as a title. The REST is treated as
data with unique values. So if your title matches your data, you'll see it
twice. Give it a real title, like "The List" and you'll always see "The
List" as the first item, but the rest will be unique. It has nothing to do
with values being numeric or not.
HTH
Bob Umlas
Excel MVP