function for repeated cells

H

Haz

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
 
T

T. Valko

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
 
D

DazzaData

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
 
H

Haz

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
Click to expand...
DMM5555 1
DMM5555 1

Is there a function that could return this?
 
T

T. Valko

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
 
R

Rick Rothstein \(MVP - VB\)

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,"")
Click to expand...

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
 
Top