function for repeated cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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?
 
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
 
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
 
Back
Top