Finding Duplicate Values in Multiple Columns

**krissyboy78** · 01-04-2013, 08:54 AM

I am not sure if Excel is able to do this but basically I am looking to find out which rows have some duplicate values. I have just read this back and it doesn't make a great deal of sense so I have attached an example spreadsheet.

Basically I am looking to find if E1:G1 duplicates further on down the list, hope this makes a bit more sense with the example attached.

Help would be appreciated

Thanks

**benishiryo** · 01-04-2013, 09:14 AM

hi krissyboy78, welcome to the forum. what do you want to do with them? to delete, Excel 2007 & above has a new function to Remove Duplicates:
go to Data-> Remove Duplicates. Check the option that "My data has headers". Check only the fields E:G to indicate that it is only considered a duplicate when the those are duplicated.

to highlight them, select the range you want to apply to (i.e. A2:H23)
go to Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true:
=SUMPRODUCT(--($E$2:$E$23&$F$2:$F$23&$G$2:$G$23=$E2&$F2&$G2))>=2

**Fotis1991** · 01-04-2013, 09:14 AM

Comparing eatch column separately OR all 4 columns together?

Can you tell us which row are dublicates in your example?

**krissyboy78** · 01-04-2013, 09:36 AM

Benishiryo - you are an absolute legend!!! I have been doing this manually for months on spreadsheets of over 5000 rows!!! This will now give me time to do work in my work time rather than my own time!!!! Thank you SOOOOOOOO much!!