Identifying matches in Excel using AND function

[RobinTMathew]

<TABLE style="WIDTH: 563pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=749 border=0><COLGROUP><COL style="WIDTH: 194pt; mso-width-source: userset; mso-width-alt: 9435" width=258><COL style="WIDTH: 169pt; mso-width-source: userset; mso-width-alt: 8228" width=225><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 25pt; mso-width-source: userset; mso-width-alt: 1206" width=33><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 194pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=258 height=17>Hello, I'm trying to figure out how to write a formula to identify unique cells. The information below illustrates how Excel can determine the following two addresses as unique due to the way it is written. I tried to write the formula using IF-AND Statement and incorporating LEFT. So if the following below represents A1 and A2; B1 & B2 then I'd like to write IF-AND the left of a1 by three characters is equal to the left of a2 by three characters AND left of b1 by three characters is equal to left of b2 by three characters (meaning they both have a match) then produce an output of 1 otherwise 0. This can at least help me identify potential matches from over 260k names and delete the items that are matched. If there is another way Im' open to suggestions. Thank you



1960 Digital Imaging
</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 169pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=225>





















837 FM 1960 West
</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 69pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=92>Houston</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 25pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=33>TX</TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 58pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=77>77090</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>1960 Digital Imaging , PA</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">837 FM 1960 W. </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Houston</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">TX</TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">77090</TD></TR></TBODY></TABLE>

 

[Robert Mika]

In case you provided try this:
=IF(AND(LEFT(A1,3)=LEFT(A2,3),LEFT(B1,3)=LEFT(B2,3)),1,0)


but may probably need something more sophisticated in case A1 would equal A3 and B1 equal B3.

 

[John Davis]

<TABLE style="WIDTH: 563pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=749 border=0><COLGROUP><COL style="WIDTH: 194pt; mso-width-source: userset; mso-width-alt: 9435" width=258><COL style="WIDTH: 169pt; mso-width-source: userset; mso-width-alt: 8228" width=225><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 25pt; mso-width-source: userset; mso-width-alt: 1206" width=33><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 194pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=258 height=17>Hello, I'm trying to figure out how to write a formula to identify unique cells. The information below illustrates how Excel can determine the following two addresses as unique due to the way it is written. I tried to write the formula using IF-AND Statement and incorporating LEFT. So if the following below represents A1 and A2; B1 & B2 then I'd like to write IF-AND the left of a1 by three characters is equal to the left of a2 by three characters AND left of b1 by three characters is equal to left of b2 by three characters (meaning they both have a match) then produce an output of 1 otherwise 0. This can at least help me identify potential matches from over 260k names and delete the items that are matched. If there is another way Im' open to suggestions. Thank you



1960 Digital Imaging
</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 169pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=225>





















837 FM 1960 West
</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 69pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=92>Houston</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 25pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=33>TX</TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 58pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=77>77090</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>1960 Digital Imaging , PA</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">837 FM 1960 W. </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Houston</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">TX</TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">77090</TD></TR></TBODY></TABLE>

Does this work for you?

=IF(AND(LEFT(A5,3)=LEFT(A6,3),LEFT(B5,3)=LEFT(B6,3)),1,0)

 

[RobinTMathew]

Thank you! Both worked but now you do make a good point. What would you suggest Mika?

Do you think I should go about locating duplicates a different method or by using a different formula?

Let me know. Thanks alot again to both of you.

In case you provided try this:
=IF(AND(LEFT(A1,3)=LEFT(A2,3),LEFT(B1,3)=LEFT(B2,3)),1,0)


but may probably need something more sophisticated in case A1 would equal A3 and B1 equal B3.