To check the duplicate row if the 3 cell of the row is same

[saneesh]

Hi,

can u solve this for me.

I have 3 cell with customer no:,date, and vendor no:.if any other cells have the same customer no:, date and vendor no:.it should be shown as red...

It is to find out duplicate when the 3 column is same.


Could you please help me on this.


Regards

Saneesh

 

[persol]

Saneesh,

That is the first easy question I ever read. Take a read at the below links, they will teach you the basics and an advanced feature that Excel does now a days:

http://www.youtube.com/watch?v=JK-KtWscBX4 He also has a page on his site concerning other methods:
 http://www.mrexcel.com/tip138.shtml

Then download the file labeled “Chandoo_Test (Duplicate and Stats).xls” I pasted in 
http://www.mediafire.com/?4bqckej5088snx1

This file has all the formulas that will give you exactly what you are looking for. But I like the conditional formatting better because it is faster and does not take a lot of computing power when you have a large data file.

Would you mind sharing with me the purpose of your endeavor? Perhaps you and I are working on a similar task.


Cheers,

Persol

 

[persol]

Saneesh,


also, consider using the following formulas:


=IF(COUNTIF(B:B,B2)>1,"Duplicate","")


or


=IF(COUNTIF($B$2:$B$200,B2)>1,"Duplicate","")


Both will detect duplicate numbers, labels, names, amounts, etc.


Cheers,

Persol

 

[persol]

Sorry Saneesh, I forgot to add the following:


in your original question you indicated that "I have 3 cell with customer no:,date, and vendor no …. in a cell", how would you detect duplicates. That is easy, again, the formula I provided in my earlier post will give you the results. Except that if you want the contents of 3 cells into one, use the =concatenate() formula or simply enter the following formula into D3

=A3 & " | " & B3 & " | " & C3. This will combine all contents from the three cels into one. Then on E3, enter the formula I posted earlier. That should do the trick.


Now, seriously, cheers!!!

Persol

 

[asa]

For the countif example, you can use the same logic you would use for multiple fields in a conditional formatting rule. Instead of concatenating fields, it is both less demanding on the cpu (no concatenation to do) and more precise (if any of your fields had a "|" in them the concatenation method would fail):


for Excel 2007+, just use COUNTIFS for multiple fields:

=COUNTIFS(cust# data range,cust#,date data range,date,vendor # data range,vendor #)>1


will be true when all three fields are duplicated


You can also use this as a data validation rule to prevent entry of a value in any of those 3 fields if it will cause duplicate record.


For Excel versions prior to 2007, you can do the same with SUMPRODUCT or an array formula.  SUMPRODUCT could go like this:

=SUMPRODUCT((cust#_data_range=cust#)*(date_data_range=date)*(vend#_data_range=vendor#))>1

Careful use of absolute and relative references in your cell and range references will allow the above to be applied to multiple cells and rows, either as in-cell formulas, conditional formatting rules, or data validation rules.


Asa

 

[NARAYANK991]

Hi asa ,


Thanks for the COUNTIFS tip. The following link explains it all.


http://spreadsheets.about.com/od/somethingiffunctions/ss/2011-06-04-Excel-2010-Countifs-Function-Step-By-Step-Tutorial.htm


Narayan

 

[asa]

I said in my last comment you could use my example as a data validation rule... as a validation rule, you would actually want to check that there was exactly one record, not that there were duplicates!


For that, change the >1 at the end to =1


Asa

 

[saneesh]

hi persol,


also, consider using the following formulas:


=IF(COUNTIF(B:B,B2)>1,"Duplicate","")


or


=IF(COUNTIF($B$2:$B$200,B2)>1,"Duplicate","")


Both will detect duplicate numbers, labels, names, amounts, etc.


your above formula works but...i don want to color the row of duplicate with red .for easy identification..

 

[persol]

Hi Saneesh,


I like your suggestion even better. Thank you so much.

BTW - what do you mean "but...i don want to color the row of duplicate with red .for easy identification.."?

 

[saneesh]

Hi persol,


Can you set me a formula to automatically color the duplicate row along with the following formula


=IF(COUNTIF(B:B,B2)>1,"Duplicate","")

 

[Luke M]

The Conditional format formula would be, assuming you start on row 2:

=COUNTIF(B$2:B2,B2)>1


This will evaluate to true (and thus color) any cells where there is a duplicate value.