View Full Version : Pull out the values which are not Unique
sindhuja
09-21-2009, 03:00 PM
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
09-21-2009, 04:14 PM
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
09-21-2009, 05:33 PM
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
09-22-2009, 12:57 AM
That is what I did, so what is wroing with my suggestion?
saurav.falia
09-22-2009, 07:48 AM
Hello Buddy, I am working on it
sindhuja
09-22-2009, 08:30 AM
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
09-22-2009, 08:54 AM
You can select all of the data at one time and apply the format.
sindhuja
09-22-2009, 01:11 PM
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
09-22-2009, 02:06 PM
Sindhuja,
Glad it's sorted. I was actually thinking of using that as a conditional formatting formula, and highlighting those rows.
bruinenat
10-12-2009, 05:38 AM
thx
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.