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
--
Gary''s Student - gsnu2007k


"Jonny Ross" wrote:

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

Excel 2007
Filter on count of one.
No formulas or code or helper column was used.
Many other built-in criteria are available.
http://www.mediafire.com/file/jzyzhziywhy/01_10_09.xlsx

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,"")

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Jonny Ross" wrote:

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

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

"Jonny Ross" wrote in message
...
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