Perfect Match Column A and B then highlights duplicates single time

[majidsiddique]

Hi All,

i need your help to find out duplicates Column A and B. but i used simple CF and highlight duplicates value, in this same column values highlights.
i need to perfect match single value column B to match column A. example 280 value column B lies 40 time and column A 43 time so i want highlight 40 values match column A and 2 time values column A is extra after using filter.

i used formula =ISNUMBER(MATCH(B2,$A$2:$A$385,0)) for perfect match but this is also showing TRUE.
can you tell me how i can do highlights duplicates values exact match. if any value not lies in column A not highlight

hope you understand my point.

Thanks

[avk]

In "D2"

Please Login or Register  to view this content.

copy paste down.

[majidsiddique]

Hi Avk,
in your formula apply in E2 but value 937, 2 time in column B and 1 time for column A. please check, only one 937 value will be "ok" or highlight.

Thanks.

[Ace_XL]

Try in D2

=AND(COUNTIF(A:A,B2)>0,COUNTIF(B$1:B2,B2)>1)

[majidsiddique]

Thanks for the reply Ace-XL,
i am using this your formula, but there is little confusion, value 280 when apply filter column B to check count value excel show 40 time then apply column A its show 43 time,
i apply formula column C and D to both side, formula FALSE filter column D only one value 280 show on there, one is missing.
can you help where is the problem.
second value 937 show FALSE value both column D and column C formula perfect show one TRUE one FALSE.
please see the file
Thanks

[shukla.ankur281190]

May be it is because the = is not included in provided formula.

Try

C2=AND(COUNTIF(A:A,B2)>0,COUNTIF(B$1:B2,B2)>=1)

[majidsiddique]

Hi Shukla.ankur281190,
Thanks for the reply,
i applied your formula, so many values are disturb, value 937 double time in column B BOTH are TRUE, its wrong because one TRUE and second must be wrong, column A only one value 937.

Thanks.

[AliGW]

Maybe this?

=COUNTIF(B$1:B2,B2)<=COUNTIF(A:A,B2)

[majidsiddique]

Hi AliGW,
i used formula column E, working perfect to check value 280 after filter column B, 40 time TRUE Perfect,same of value 937, when i applied column F wise versa to check column F, =COUNTIF(A$1:B2,A2)<=COUNTIF(B:B,A2)
after using filter column A, 24 time value FALSE column F , why this, only 3 time value increase by column B,
column A = 43 and Column B = 40 value "280"

Thanks

[samba_ravi]

Please find the attached file
use below formula to match Sales-Tax-Credit Vs Credit Actual
=COUNTIF(A$2:A2,A2)<=COUNTIF(B$2:B$449,A2)
and
=COUNTIF(A$2:A$385,B2)>=COUNTIF(B$2:B2,B2)
to match Credit Actual Vs Sales-Tax-Credit Vs

[AliGW]

Time to attach your workbook again.

[majidsiddique]

Thanks Samba_ravi.
its really perfect. thanks a lot.

Thanks