Hi All!
I want to see duplicates when 2 columns have 2 different values. So basically comparing 2 rows. I have attached an excel for reference.
Thanks!
Hi All!
I want to see duplicates when 2 columns have 2 different values. So basically comparing 2 rows. I have attached an excel for reference.
Thanks!
one solution would be a helper column - see attachment
in your sample your match is wrong. You could not see it but one "Apple" has a space after the name.
you could ignore it if you use the trin- function additionally
cheers
Hi,
in order to find the match in 2 columns - I created column E to combine both C&D into the same cell.
in the "Match" column I applied the following formula:
=+IF(COUNTIF($E$5:$E$9,E5)>1,"Yes", "No")
You can also apply conditional formatting onto column E (format onlu unique or duplicate values), so the duplicate cells will be colored.
You may do it also without helper column. the formula for E5 (and copy down):
Formula:Please Login or Register to view this content.
Best Regards,
Kaper
Nicem Kaper. can you please explain the formula? what is the role of "--" that you have put after the sumproduct?
The result of ($C$5:$C$9 & $D$5:$D$9=C5 & D5) will be an array of boolean (False/True) values. -- forces excel to convert them to array of (1/0) values and then sumproduct just sums 1's in this array
write in a cell =true (you will see TRUE as result)
then =-true (and result will not be FALSE as one could expect, but -1)
then =--true and you will see 1
of course other methods to convert array of boolean values into array of numbers (0 for false and 1 for true) could be used, like: N($C$5:$C$9 & $D$5:$D$9=C5 & D5) or 1*($C$5:$C$9 & $D$5:$D$9=C5 & D5)
thank you!!!!!!!!
Thanks Kaper!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks