Excel Filtering Phantoms

April 6, 2005 4:00 PM

I was hoping someone might have insight into why, when I filter row of text in Excel for Unique Values, i.e., no duplicates displayed, I still get some duplicate rows. Has anyone experienced this? Again, it's text that I'm working with; not numbers.
posted by ParisParamus to computers & internet (4 comments total)

Excel is a little funny with how it determines equality sometimes. Formatting and whitespace all come into play and can muck up your equality test.

Let's say that A1 and B1 should be the same, according to you, but Excel thinks they're different.

To see what I'm talking about, go to the Visual Basic editor in Excel (Alt+F11) and then go to View > Immediate Window.

Type:
?Range("A1").Value (then hit enter)

Then try it for B1. You should see what Excel thinks the value is. If you notice any whitespace on either side of the value printed out, then that's one thing that could be throwing it off. Another thing would be the formatting. Make sure both cells are formatted as Text. In addition, you sometimes have to actually enter a cell as if you were going to edit it, and then re-commit the cell's value in order for your formatting change (if you made one) to stick.

So....it's definitely not perfect.

Also, you can ask the VB editor's Immediate window what it thinks by doing something like:
?Range("A1").Value = Range("B1").Value
...which would print a True or a False.
posted by odinsdream at 4:07 PM on April 6, 2005

I've often experienced what odinsdream describes. It's always a little hidden apostrophe or comma that for some reason doesn't show up on the worksheet. Usually I find it happens when I'm copying/pasting or importing from Access.
posted by loquax at 6:01 PM on April 6, 2005

My guesses would be:

Lack of column headings (but excel should complain about this, and it should give you only one entry duplicated)
Whitespace (as mentioned)

I'll often just stick another column alongside the data, fill it with "=TRIM(cellreference)" and the copy/paste-special-as-values back over your original data.
posted by pompomtom at 7:28 PM on April 6, 2005

Hmmm....I just reformatted all cells as "text," and at least some of the duplicates disappeared. Maybe that did it?
posted by ParisParamus at 11:20 PM on April 6, 2005