function for repeated cells
 

hi

I have a spreedsheet that has cells that are repeated amongst others I want
to write a function that will identify these.
if column A has this data in it how can I pick up the identical cell in
Column B
DMM9844
DMM9844 1 - this what I want in col B.
DMM9756
DMM6456 1
DMM6747
DMM3463
DMM4574

i think it may be a sumif function but i can't get it to work. Can anybody
help?
Thanks

function for repeated cells
 

Are the repeated values repeated only once?

Will you ever have this:

DMM9844
DMM9844
DMM9844
DMM9844
DMM9756
DMM6747

What result would expect on the above data?

Biff

"Haz" wrote in message
...
hi

I have a spreedsheet that has cells that are repeated amongst others I
want
to write a function that will identify these.
if column A has this data in it how can I pick up the identical cell in
Column B
DMM9844
DMM9844 1 - this what I want in col B.
DMM9756
DMM6456 1
DMM6747
DMM3463
DMM4574

i think it may be a sumif function but i can't get it to work. Can anybody
help?
Thanks

function for repeated cells
 

Hi,

if b2 is the start of your data then use, from c2

=countif($b$2:$b$XXX,b2)-1 where xxx is the bottom row

there are variants but this should tell you beside every value how many
times it occurs elsewhere

Cheers

Dazza

------------------------------------------------------------
Please register if this has been helpful.
"Haz" wrote:

hi

I have a spreedsheet that has cells that are repeated amongst others I want
to write a function that will identify these.
if column A has this data in it how can I pick up the identical cell in
Column B
DMM9844
DMM9844 1 - this what I want in col B.
DMM9756
DMM6456 1
DMM6747
DMM3463
DMM4574

i think it may be a sumif function but i can't get it to work. Can anybody
help?
Thanks

function for repeated cells
 

hi
yes it is possible that I have 2 of more cells that are the same, and want
to be able to see all these repeated cells in column B only.
Like the following:
DMM9844 1
DMM9844 1
DMM9844 1
DMM9844 1
DMM9756
DMM6747
DMM5555 1
DMM5555 1

Is there a function that could return this?
"T. Valko" wrote:

Are the repeated values repeated only once?

Will you ever have this:

DMM9844
DMM9844
DMM9844
DMM9844
DMM9756
DMM6747

What result would expect on the above data?

Biff

"Haz" wrote in message
...
hi

I have a spreedsheet that has cells that are repeated amongst others I
want
to write a function that will identify these.
if column A has this data in it how can I pick up the identical cell in
Column B
DMM9844
DMM9844 1 - this what I want in col B.
DMM9756
DMM6456 1
DMM6747
DMM3463
DMM4574

i think it may be a sumif function but i can't get it to work. Can anybody
help?
Thanks

function for repeated cells
 

Try this:

Assume range of data in column A is A1:A10.

Enter this formula in B1 and copy down as needed:

=IF(COUNTIF(A$1:A$10,A1)1,1,"")

Biff

"Haz" wrote in message
...
hi
yes it is possible that I have 2 of more cells that are the same, and want
to be able to see all these repeated cells in column B only.
Like the following:
DMM9844 1
DMM9844 1
DMM9844 1
DMM9844 1
DMM9756
DMM6747
DMM5555 1
DMM5555 1

Is there a function that could return this?
"T. Valko" wrote:

Are the repeated values repeated only once?

Will you ever have this:

DMM9844
DMM9844
DMM9844
DMM9844
DMM9756
DMM6747

What result would expect on the above data?

Biff

"Haz" wrote in message
...
hi

I have a spreedsheet that has cells that are repeated amongst others I
want
to write a function that will identify these.
if column A has this data in it how can I pick up the identical cell
in
Column B
DMM9844
DMM9844 1 - this what I want in col B.
DMM9756
DMM6456 1
DMM6747
DMM3463
DMM4574

i think it may be a sumif function but i can't get it to work. Can
anybody
help?
Thanks

function for repeated cells
 

That has worked perfectly
Many thanks for your help

"T. Valko" wrote:

Try this:

Assume range of data in column A is A1:A10.

Enter this formula in B1 and copy down as needed:

=IF(COUNTIF(A$1:A$10,A1)1,1,"")

Biff

"Haz" wrote in message
...
hi
yes it is possible that I have 2 of more cells that are the same, and want
to be able to see all these repeated cells in column B only.
Like the following:
DMM9844 1
DMM9844 1
DMM9844 1
DMM9844 1
DMM9756
DMM6747
DMM5555 1
DMM5555 1

Is there a function that could return this?
"T. Valko" wrote:

Are the repeated values repeated only once?

Will you ever have this:

DMM9844
DMM9844
DMM9844
DMM9844
DMM9756
DMM6747

What result would expect on the above data?

Biff

"Haz" wrote in message
...
hi

I have a spreedsheet that has cells that are repeated amongst others I
want
to write a function that will identify these.
if column A has this data in it how can I pick up the identical cell
in
Column B
DMM9844
DMM9844 1 - this what I want in col B.
DMM9756
DMM6456 1
DMM6747
DMM3463
DMM4574

i think it may be a sumif function but i can't get it to work. Can
anybody
help?
Thanks

function for repeated cells
 

You're welcome. Thanks for the feedback!

Biff

"Haz" wrote in message
...
That has worked perfectly
Many thanks for your help

"T. Valko" wrote:

Try this:

Assume range of data in column A is A1:A10.

Enter this formula in B1 and copy down as needed:

=IF(COUNTIF(A$1:A$10,A1)1,1,"")

Biff

"Haz" wrote in message
...
hi
yes it is possible that I have 2 of more cells that are the same, and
want
to be able to see all these repeated cells in column B only.
Like the following:
DMM9844 1
DMM9844 1
DMM9844 1
DMM9844 1
DMM9756
DMM6747
DMM5555 1
DMM5555 1

Is there a function that could return this?
"T. Valko" wrote:

Are the repeated values repeated only once?

Will you ever have this:

DMM9844
DMM9844
DMM9844
DMM9844
DMM9756
DMM6747

What result would expect on the above data?

Biff

"Haz" wrote in message
...
hi

I have a spreedsheet that has cells that are repeated amongst others
I
want
to write a function that will identify these.
if column A has this data in it how can I pick up the identical
cell
in
Column B
DMM9844
DMM9844 1 - this what I want in col B.
DMM9756
DMM6456 1
DMM6747
DMM3463
DMM4574

i think it may be a sumif function but i can't get it to work. Can
anybody
help?
Thanks

function for repeated cells
 

Try this:

Assume range of data in column A is A1:A10.

Enter this formula in B1 and copy down as needed:

=IF(COUNTIF(A$1:A$10,A1)1,1,"")

A minor change the OP may want to consider is this...

=IF(COUNTIF(A$1:A$10,A1)1,COUNTIF(A$1:A$10,A1),"" )

which will show the total number of repeats (if there are 2 or more) instead
of just a 1... having the count may make whatever the OP is doing with the
duplicates easier as he will know how many need to be processed in total.

Rick