comparing values

[hanyou.lin]

Hi guys, on a sheet i have 2 column with values.
For e.g

DevID DevNum
123 s123
123 s234
987 sabc
987 sabc

Is there a way where i can compare the values in DevNum with the same DevID
to see if theres any duplicated values using macro?

Thanks all.

[Richard Buttrey]

Hi,

Why do you need a macro. A simple formula in C2

Please Login or Register  to view this content.

and copied down, will return either True or False. Then just filter column C for 'True' values.

HTH

[JamesT1]

Another simple method is,

in the third colum,

Please Login or Register  to view this content.

this will tell you which items are duplicates

[hanyou.lin]

Oh i'm sorry guys, i think you misunderstood me. What i meant to compare is to compare all the values in Column B DevNum with the same corresponding DevID to check for duplicate.

E.g
DevID DevNum
123 sabc
123 sabc
123 sdef
456 shij
456 sdlo
456 sgfc

So in this case, For all DevID "123", corresponding DevNum has duplicates value which is "sabc"

[shg]

Maybe

=IF(SUMPRODUCT(--($A$2:$A$7 & $B$2:$B$7 = A2 & B2) ) > 1, "Dup", "")

[hanyou.lin]

Thanks alot Shg do i don't think this would work as the number of values in DevID is dynamic. So sometimes i can have 5 DevID with the same values and sometime more or less.

[shg]

Then use a dynamic named range.

[hanyou.lin]

Sorry but how do i do that?

[shg]

Define

Tbl Refers to: =OFFSET(Sheet1!$A$1, 1, 0, COUNTA(Sheet1!$A:$A) - 1, 2)

... and change the formula to

=IF(SUMPRODUCT(--(INDEX(Tbl, 0, 1) & INDEX(Tbl, 0, 2) = A2 & B2) ) > 1, "Dup", "")

[hanyou.lin]

Hey Shg thanks again, but i seriously think i would need a macro. Tried running the code u provided but it kinda "Hang" cause i had to copy that formula in a limit of 10 000 rows

[shg]

OK.

Please Login or Register  to view this content.