Duplicate Issue...

[jskushawah]

I have list with duplicate values in col A:A, in col B:B i want to place a formula which return duplicate values from col A:A, how will it possible ?

 

[sreekhosh]

Just try this formula,


=if(countif($a$1:$a1,a1)>1,a1,'''')


Regards


Sreekhosh

 

[sdsurzh]

Hi,


Assume your data on A Column paste the below formula in B Column it will show duplicate values.


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


Thanks,

Suresh Kumar S

 

[sdsurzh]

Hi,


You can also refer the below link:


http://chandoo.org/wp/2012/06/28/check-list-for-duplicate-numbers/


Thanks,

Suresh Kumar S

 

[jskushawah]

Thanks to all ..

all formulas are working....

 

[juanito]

Instead of using an additional column you can also highlight duplicates using conditional formatting - formulas same as the ones already shown


- Juanito

 

[sdsurzh]

Yes correct Junaito we can also use conditional fromat.


Use Excel conditional formatting to highlight duplicate entries in a column:

Select range A2:A11

Choose Format|Conditional Formatting

From the first dropdown, choose Formula Is

For the formula, enter


=COUNTIF($A$2:$A$11,A2)>1


Click the Format button.

Select a font colour for highlighting.

Click OK, click OK


Thanks,

Suresh Kumar S

 

[nazmul_muneer]

Thik your data from A2

Code

a

b

c

d

de

d

c

d

de


In B2 write the formula and copy down upto B10

=IF(AND(COUNTIF($A$1:$A$10,A2)>1,COUNTIF($A$1:A1,A2)<1),ROW()," ")

Again in C2 write the formula and copy down upto C10

=IFERROR(INDEX($A$1:$A$10,SMALL($B$1:$B$10,ROW(A1))),"")


----Muneer

 

[nazmul_muneer]

jskushawah

You can also try the 2nd option

In E2 write the formula and copy down upto E10

=IF(AND(COUNTIF($A$1:$A$10,A2)>1,COUNTIF($A$1:A1,A2)<1),MAX($E$1:E1)+1," ")

Again in F2 write the formula and copy down upto F10

=INDEX(A:A,MATCH(ROW(A1),E:E,0))


The above both formula return you individual duplicate value


----Muneer

 

[Krishnakumar]

Try this array formula


Assume your data in A2:A26, In B2 and copied down,


=INDEX($A$2:$A$26,SMALL(IF(FREQUENCY(MATCH($A$2:$A$26,$A$2:$A$26,0),ROW($A$2:$A$26)-ROW($A$2)+1)>1,ROW($A$2:$A$26)-ROW($A$2)+1),ROWS(B$2:B2)))


Kris

 

[nazmul_muneer]

@Kris

Have you tested your formula that you posted?


--Muneer

 

[SirJB7]

@nazmul_muneer

Hi!

I've seen this formula before, here in this site.

Regarding your question, I did and it works fine. Are you facing any issues?

Regards!

 

[Krishnakumar]

@ Muneer,


Have you tested the formula ? If so what you get ?


Kris

 

[nazmul_muneer]

SirJB and Kris,

Actually i made a mistake on data typing. I typed data from A2 to A10 but i wrote the formula for the range A2 to A26 so it returns #N/A.


When i understood my mistake, i changed it.

It works fine


Regards,

---Muneer