View Full Version : Excel formula needed

22-01-2013, 10:19 PM
I have a spread sheet with around 150 rows of surname in Col A and first name in Col B over in Col M i have added ColA & Col B together eg SmithJohn
Now i need to check if any of the names in the 150 rows are repeated. Ideal if the name in Col A & B were highlighted or changed colour if possiable.
we are just looking for any repeats in Col M
Hope this makes sence.
TIA wayne

22-01-2013, 10:34 PM
One way http://howto.mellowcart.com/

Another way from Brainy box son ----- Select the Col - Conditional Formatting - Highlight cell rules- Duplicate Values - Click the drop down box to select the colour you want duplicates to show.

22-01-2013, 10:52 PM
Add to above -- the example pictures Taken from office 2013, but 2007 & 2010 are the same way:

4688 4689

Had to use a different Computer to upload the pics, the PF1 unloader doesn't like IE10.:mad:

23-01-2013, 07:55 AM
Thanks wainuitech that works great.

23-01-2013, 09:04 AM
You coud also just enable filtering on the title row and sort by name - would be very easy to see duplicates then as they'd be in adjacent cells.

I'll have to try and remember that conditional formatting trick, I often have to search through large sorted spreadsheets looking for duplicates and I've always added a column and used a formula like =IF(B3=B2,"!","") to mark the duplicates. I also have to check for gaps in a numerical sequence to show missing rows and I've used =IF(B3-B2<>1,"!","")