How find duplicate values ?

[jskushawah]

I have a number list in Col A which has some duplicate values. I used =COUNTIF(A1:A46,MODE(A1:A46)) for verifying duplicate values.

now i want to all duplicate values in col B, so pls suggest me is this possible ??

if yes then how ?

 

[dohsan]

Hi,


I'm not sure 100% if I understand what your after, but should be several things you can do.


To simplify make a named range called list for your A1:a46


In B2


Distinct List (A list containing no duplicates)


=IFERROR(INDEX(List,MATCH(0,IF(ISBLANK(List),"",COUNTIF($B$1:B1,List)),0)),"")


Enter as array


Or


Unique List (The ones in the list which have no duplicates at all)


=IFERROR(INDEX(List, MATCH(0, COUNTIF($C$1:C1, List)+(COUNTIF(List, List)<>1), 0)),"")


enter as array


or


and I think this may be what your asking for, you can tweak this to show the ones which do have duplicates, I think this should work:


=IFERROR(INDEX(List, MATCH(0, COUNTIF($D$1:D1, List)+(COUNTIF(List, List)=1), 0)),"")


again enter as array


The unique/not unique ones will bring back 0 when a blank is in the list, the original factors it in

 

[jskushawah]

Actually sir,

i want only those values in col B which are more than one time in "list".

 

[dohsan]

The Third formula should generate a list for that

 

[jskushawah]

sorry to say, but sir i am vary poor in using array formulas, so pls suggest me how i use the third formula, i can't understand why this formula contains Col D and cell $D$1 while my list is in A1:A46 and i want it in Col B, don't mind but kindly suggest me .....

 

[dohsan]

Apologies, when I was trying them out I had them in columns B, C and D respectively


if you put the following in B2 it should work


=IFERROR(INDEX(List, MATCH(0, COUNTIF($B$1:B1, List)+(COUNTIF(List, List)=1), 0)),"")


+ ctrl + alt + enter

 

[shailyog]

to array enter formula-

press Ctrl+ Shift+ Enter