delete duplicate values - leaving unique records only i.e. recordspresent once only

  • Thread starter Thread starter Jonny Ross
  • Start date Start date
J

Jonny Ross

hiya,

i see lots of posts resolving the following:-

a
a
b
c
d
d
e

to

a
b
c
d
e

however what i want to do is take a column and turn

a
a
b
c
d
d
e

into

b
c
e

i.e showing me data that is present once only...

any ideas how i do this? excel 2007

many thanks
 
Say we have in A1 thru A20:

a
a
b
c
d
d
e
f
g
g
g
h
h
i
j
j
j
k
l
l

In B1 enter:
=IF(COUNTIF(A:A,A1)=1,1,"")

In B2 enter:
=IF(COUNTIF(A:A,A2)=1,MAX(B$1:B1)+1,"") and copy down

We see:

a
a
b 1
c 2
d
d
e 3
f 4
g
g
g
h
h
i 5
j
j
j
k 6
l
l

Each of the required rows has been asigned a unique number. Finally in C1
enter:

=IF(MAX(B:B)<ROW(),"",OFFSET($A$1,MATCH(ROW(),B:B),0)) and copy down

We now see:

a c
a d
b 1 f
c 2 g
d j
d l
e 3
f 4
g
g
g
h
h
i 5
j
j
j
k 6
l
l

Only those items appearing once appear in column C
 
Hi,

The second suggestion requires a formula to do the count.

If you are using Excel 2007, you can highlight the duplicated (leaving the
uniques unhighlighted) by highlighting the range and choosing Home,
Conditional Formatting, Highlight Cell Rules, Duplicate Values. ( a new
command in 2007)

And if you want to use a formula
=IF(COUNTIF(A$1:A$13,A1)=1,A1,"")
 
Hi,

You can also try the folliwing. First of all, please ensure that you have a
suitable heading for the range (it is a good practise to do so). Therefore,
assume that the data below is in range B3:B9. In B2, type numbers.

Now in cell B13, type the following formula =countif($B$2:$B$9,B3)=1. In
cell B12, type Criteria. Now perform the following steps:

1. Go to Data > Sort and Filter > Advanced.
2. Click on the "Copy to another location"
3. In the list range, select B2:B9
4. In criteria range, type B12:B13
5. In the copy to box, select any blank cell.

Hope this helps.



--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
Back
Top