Excel 2007 : Problem with removing duplicates

**emil9216** · 02-15-2012, 03:45 PM

I have text in column A and column B. In column A i have cells with duplicate text. If i try to remove the duplicates in column A and choose "expand the selection" it doesn't find duplicates. How can i remove the duplicates when i expand the selection?

Here is an exemple file:

example.xlsx

Thanks!

**Paul** · 02-15-2012, 05:52 PM

Hi Emil,

Your sample data doesn't have duplicates of both columns. When you use the Remove Duplicates function, and you "Expand the selection" (or simply select columns A and B together) it compares all of the data in both columns.

So you have
Red.....Google
Blue....Google
Red.....Yahoo
Blue....Yahoo

Even though you have two Google references in column B, you don't have matching references in column A. If you had:

Blue.....Google
Red.....Yahoo
Blue.....Google

Then it would remove the second instance of Blue & Google.

To fix (I think - in your case):

Select columns A and B, then click Remove Duplicates. In the pop-up window un-check the box next to column B and click OK. That should remove the duplicates in column A along with the associated values in column B.

**emil9216** · 02-15-2012, 06:47 PM

Ok, but this will not work if i don't have duplicates in column B.

For example:

red.....google.com
blue....yahoo.com
red.....cnn.com
blue....bing.com

It doesn't find duplicates in this case. I would like to know if i can remove the duplicates in column A when i don't have duplicates in column B and i am choosing the option to expand the selection.

**Paul** · 02-15-2012, 07:10 PM

What would you expect the result to be if you removed the duplicates in column A, leaving one red and one blue entry?

red.....google.com
blue....yahoo.com

?

**sushil10s** · 02-16-2012, 06:24 AM

See Attachement

**Paul** · 02-16-2012, 01:41 PM

Note, I thought this was the original person providing a sample data. The below may not be relevant, but may help someone else so I'll leave it here.

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

I see an attachment with:

Please Login or Register  to view this content.

So it appears you simply want a list of unique values from column A entered into column C, regardless of what is in column B? You can do that with Advanced Filter instead.

With a header in A1 (e.g. "Color"), select column A and click on Data > Sort & Filter section > Advanced. For Action, choose to Filter the list in-place or Copy to another location. 'List Range' would be A1:A5 and 'Copy To' would be wherever you want to put the unique values, e.g. $C$1. Check the box for 'Unique Records Only' and click OK.