removing 'all' duplicates

[Jason_C]

I have a column of data which has multiple duplicates, I wish to remove all of the duplicates.

say I have


aaa
aaa
bbb
bbb
ccc
ccc
ccc
ddd
eee
fff

then I would be left with just the records that never had a duplicate.

ddd
eee
fff

How would I do this please.

[twiggywales]

can you use helper columns of do you need a single cell formula

[twiggywales]

if you can use an Array formula you can do this

Please Login or Register  to view this content.

(this reefers to the formula in my spreadsheet see below)

remove dup.xlsx

[SalientAnimal]

Would using the built in Excel "Remove duplicates" function under the DATA Tab not help you here?

[Sam Capricci]

you don't say what version of excel you have (i think the remove duplicates function appears in 2007 and later). It would take me a couple steps to do it but i'd copy the column, paste it elsewhere, then use remove duplicates, then do a countif against the original list. Then use a vlookup with the original and anywhere where the cell returns a value greater than 1, use conditional formatting to color code the cell. Then final step filter on those colored then delete them. Rather a long way to get the job done and i'm sure there are shorter ways.

[Jason_C]

I am wanting to have left everything that never had a duplicate.

so if it was

aa
aa
bb


then I would have just

bb

left, all duplicates (aa) to be taken out of the column

i have version 2010.

[twiggywales]

did you look at my file. i think that that does exactly what you need.

if you upload a ample book we can look at it and see more clearly your issue

[boopathiraja]

Hi, Apply the below formula in c3 and drag down, filter for false and remove it

Please Login or Register  to view this content.

[twiggywales]

sorry double

[SalientAnimal]

Something else you can try is the formula below, then do a sort and remove all the 0

Please Login or Register  to view this content.

[Jason_C]

yes that would be right, I wish to remove all the doubles... sorry my bad.

[Sam Capricci]

my version, see attached.

[twiggywales]

is there any reason mine wouldn't work?

its a single cell solution and should do everything you need. if not i might be able to tweak it

[Jason_C]

I tried that but it did not show any single results. all fields were blank. I shall submit a sample...

[canapone]

Hi,

same twiggywales's formula and approach without array status:

With reference with your file in E2 and below:

Please Login or Register  to view this content.

Regards

[canapone]

Hi again

in the attachment formulas using AGGREGATE.

Regards

[Sam Capricci]

your sample with results

[Punnam]

Please Login or Register  to view this content.

in E2

Punnam

[twiggywales]

another way to do what you need it to put this in your cell and drag to suit

Please Login or Register  to view this content.

[Pete_UK]

There are some complicated formulae here. I think the easiest will be to put this formula in B2:

=COUNTIF(A:A,A2)

then copy down to the bottom of your list in column A (double-click the fill handle). Then apply a filter to column B, and use "does not equal 1" as the criteria. then select all the rows that are visible and press the <delete> key. Then you can select All on the filter drop-down to see the remaining records as in your sample.

( If you want the remaining records to be bunched up with no spaces, then you can click the delete rows icon, rather than use the <delete> key ).

Then you can remove column B.

Hope this helps.

Pete

[Jason_C]

yes that seems to work for me. thanks

[Punnam]

Hi Jason_C ,

You have received answers from many forum members, your post 21 is referring to whom ?

Punnam

[Jason_C]

Attached is the method I used, but the last step is to get the content of column B moved over also.

Thank you all for your support on this.

[Punnam]

Please Login or Register  to view this content.

In F2

Punnam

[Jason_C]

perfect, thank you

[mehmetics]

what about this formula

=IF(COUNTIF(A:A,A2)>1,"",A2)

paste it in E2 drag down.

[newdoverman]

If your data is in column A from A1 to A10 this if entered in B2 and copied down will return a listing without blanks of the values that appear only once in column A

Please Login or Register  to view this content.