My if/and function isn't working???

[Sbova0226]

this is the function i am trying to use =if(AND(A2=A3,L2=L3,M2=M3,N2=N3),"DUPE",0))


A= id
L= street address
M=unit number to the address
N=city

I am trying to do this and it is leaving me with a lot of duplicates.

[Sbova0226]

i only have 1 paranthesis at the end not the double

[mubashir aziz]

Hi,

formula is working nicely. can you be more specific to explain what you exactly needs .... here in above example IF condition will give you dup if all cells will be equal but if you want to show dupe if any one cell is equal then use OR ....
But good approach is to attach a sample file with your requirements ....

[Paul]

What do you mean it is leaving you with a lot of duplicates? What were you expecting it to do?

Your formula simply checks those four columns in that row, and if they match the cells from the rows below, show "DUPE" in the cell with the formula. If not, show 0.

[JBeaucaire]

The problem I always run into when comparing text strings is invisible but pesky extra spaces. The only way to be sure is to TRIM() every reference. A pain, but it gives you something reasonable to compare.

Try this:

=IF(AND(TRIM(A2)=TRIM(A3),TRIM(L2)=TRIM(L3),TRIM(M2)=TRIM(M3),TRIM(N2)=TRIM(N3)), "DUPE", 0)


Or maybe this:

=IF(TRIM(A2&L2&M2&N2)=TRIM(A3&L3&M3&N3),"DUPE", 0)

[Sbova0226]

Sorry it is leaving me with a lot of dupes when I filter with 0. I am trying to filter out duplicate ids with the same exact address.

[JBeaucaire]

I would use this instead:

=IF(OR(TRIM(A2&L2&M2&N2)=TRIM(A3&L3&M3&N3),A2=""),"DUPE", 0)
...copied all the way down.

This is text string data. There is always going to be stuff you have to fix. Like rows 235-247...different spellings of the same address.

76-77, different city for the same address?

103 and 107 match, but aren't adjacent, so your formula doesn't take that into account. Perhaps sorting the data by the address field?

Address books are always fun to edit.

[mubashir aziz]

Try this code ... hope it will solve your problem ......

R2=IF(SUMPRODUCT(($A$2:$A$2493=A2)*($L$2:$L$2493=L2)*($M$2:$M$2493=M2)*($N$2:$N$2493=N2))>1,"DUPE","")

copy this code in R2 and drag downward ......

[JBeaucaire]

10k calculations per cell...and 2500 of those....egad! No, just sort the data and use a formula like the one already being attempted.

Egad!