# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  Formula for finding duplicates

## djfatboyfats

I have excel sheets that will show a customer account number an ammount they need to pay and there full name and a date when the payment is expected, is there a formula that will find duplicates, if the same entry has been put in twice

Thanks

FBF

----------


## NBVC

=IF(Countif(A:A,A1)>1,"Duplicate Exists","")  copied down

Checks to see if what is in A1 exists more than once in column A

----------


## djfatboyfats

This is great thanks,

But is there any way that it will only show the duplicates and not the orginal

Thanks

FBF

----------


## NBVC

> This is great thanks,
> 
> But is there any way that it will only show the duplicates and not the orginal
> 
> Thanks
> 
> FBF



Okay, try:




```
Please Login or Register  to view this content.
```



copied down.  This first record will NOT be flagged, but any duplicates thereafter will.

----------


## djfatboyfats

No still coming up with 4 entrys instead of 2

----------


## Jwalters

I tried using this formula for a similar solution I need. I have two columns of data and I need to find if a number in the first column appears again in the second column (but is not in the same row). I changed the formula to this:

=IF(COUNTIF(F:F,G2)>1,"Duplicate Exists","0")

I thought that would show if the value in G2 appears anywhere in column F, but it only comes back as dupicate when the value in column G is zero. How should I adjust the formula?

----------


## NBVC

> No still coming up with 4 entrys instead of 2



Can you attach a sample worksheet (zipped XL2003 or earlier) file showing this result.

----------


## NBVC

> I tried using this formula for a similar solution I need. I have two columns of data and I need to find if a number in the first column appears again in the second column (but is not in the same row). I changed the formula to this:
> 
> =IF(COUNTIF(F:F,G2)>1,"Duplicate Exists","0")
> 
> I thought that would show if the value in G2 appears anywhere in column F, but it only comes back as dupicate when the value in column G is zero. How should I adjust the formula?



If you're only looking to see if the item in G2 exists at all in column F, then you want this

=IF(COUNTIF(F:F,G2)>0,"Duplicate Exists",0)

----------


## djfatboyfats

Attached

Thanks

FBF

----------


## NBVC

You hadn't entered the formula correctly in J4....

It should be: 


```
Please Login or Register  to view this content.
```


 copied down.

See attached.

----------


## Jwalters

Thank you so much. You saved me a few hours of work.

----------


## NBVC

> Thank you so much. You saved me a few hours of work.




No problem...but please next time start your own thread..it is against forum rules... you can always reference another thread by linking it, if you want others to see a similar thread, etc..

----------


## djfatboyfats

That is excellant just what i wanted, but one last question is there a way to have this formula but with 2 types of critiria, i.e 2 columns, where would this fit into the formula

Thanks

FBF

----------


## NBVC

> That is excellant just what i wanted, but one last question is there a way to have this formula but with 2 types of critiria, i.e 2 columns, where would this fit into the formula
> 
> Thanks
> 
> FBF



So you want it to say duplicate if the item in A4 along with the item in say E4 is duplicated?

If so, then we have to go to Sumproduct for multiple criteria....

something like:


```
Please Login or Register  to view this content.
```

----------


## djfatboyfats

No this dosent seem to work, please find attached

----------


## NBVC

Can you tell me why it doesn't seem to work?

You are comparing columns A and E, there are no duplicates with same items in cells within columns A and E that I can see.

Also, to get rid of the duplicates in the blank range...just add an IF statement...

e.g. =IF(SUMPRODUCT(--($A$4:$A4=A4),--($E$4:$E4=E4))>1,"Duplicate","")

----------


## djfatboyfats

Ok yeah sorry that was my fault, i didnt add data to one column.

How do we get rid of the duplicates in the blank range, what if formula can we use?

Sorry about this.

----------


## NBVC

> Ok yeah sorry that was my fault, i didnt add data to one column.
> 
> How do we get rid of the duplicates in the blank range, what if formula can we use?
> 
> Sorry about this.






```
Please Login or Register  to view this content.
```

----------

