Pull out the values which are not Unique

[sindhuja]

Hi All...
Unique values in column D should have unique values in Column A.
If not, need those data in sheet 2

For example in the attached file for the value 753753 in column D the Column A value shoull be the same for all..but its different and its highlighted
Have attached sample data..

Any assistance will be highly helpful

-Sindhuja

[Bob Phillips]

Use conditional formatting with a formula of

=SUMPRODUCT(--($A$1:$A2=$A2),--($B$1:$B2=$B2),--($C$1:$C2=$C2),--($D$1:$D2=$D2))<>COUNTIF($D$1:$D2,$D2)

[sindhuja]

Hi,

I have very large number of data (around 10,000 rows).
Since we are dealing with huge datas macro will be very useful...

Also for each unique value in column D there should be single value onlye.. if there is more than a single value for a particular value then that should be highlighted and pull that data into sheet 2.

Immediate help on this will be highly helpful...
Thanks in advance...

-Sindhuja:help

[Bob Phillips]

That is what I did, so what is wroing with my suggestion?

[saurav.falia]

Hello Buddy, I am working on it

[sindhuja]

Hi,

Thanks a lot and it worked for me..
I copied the formatting and used ctrl + shift + downarrow to apply the formula.. It takes more time and so do suggest me to apply the formatting and formula only to the used rows.

-Sindhuja

[Bob Phillips]

You can select all of the data at one time and apply the format.

[sindhuja]

Hi Xld,
Am all set with my requirement
I did the formatting using the below coding…



dim LastRow as long

with activesheet

lastrow = .cells(.rows.count,"A").end(xlup).row
.range("E2:E" & lastrow).formula = "=SUMPRODUCT(--($A$1:$A2=$A2),--($B$1:$B2=$B2),--($C$1:$C2=$C2),--($D$1:$D2=$D2))<>COUNTIF($D$1:$D2,$D2)"

end with



Thanks for all your assistance..

-Sindhuja

[Bob Phillips]

Sindhuja,

Glad it's sorted. I was actually thinking of using that as a conditional formatting formula, and highlighting those rows.

[bruinenat]

thx