Countif

[Ross85]

Help,

I have a spread sheet at work with multiple serial numbers on it. Through the months these serial numbers have been assigned to diffrent units. However these have not been updated in the spread sheet.
What I now have is a 100% serial holdings of these units that hold these serials. My problem is instead of having to go through each serial individually I tried =countif(,)>0 ( I'd like to point out, I do put the required cells needed for the formula in there). It's seems to work fine and nightlight the cells required. However on closer inspection through the CTRL+F. When I do a sanity check, a lot of the highlighted cells are wrong.

I don't know what's causing this, but I need this to be 100% correct. Some of the serials are upto 12 in length and as short as 4. Apologies about the picture, but having problems with laptop so doing this off iPhone.

I'd also like to point out I have excel 2013 at home but 2003 at work. I'm ok at excell very new so be patient with me. All help is brilliant

These errors are really stopping me from pushing on. [ATTACH]361884

[FDibbins]

Your pic didnt come through However, it would be far more help if you could upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
Pictures are pretty much impossible to edit (even when was can see them), and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff)

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

[Ross85]

Yes,

I realise what your saying but like I said. I'm having problems with my internet and laptop. I will try to upload the workbook tomorrow. I'd like to get all the duplicate serials highlighted. So They are clearly visible and I can then move them on the system. I have used countif. But seem to have a lot of errors. I'm using 2003

[FDibbins]

Try using Conditional Formatting with =countif(range,cell)>1

[Ross85]

Yeah I have tried that, but on closer inspection some of the highlighted cells are wrong.

[ELeGault]

Without the workbook its hard to say, but if the highlights are wrong then the original area you applied the formula to may be offset to the first reference of the Conditional Formatting (CF)

Check to make sure that is correct, Also look to make sure there are not multiple rules, one of which is throwing off the sheet.
I know a common mistake I see made is that someone will try to apply CF to an entire column, and then when they place in their logic statement they attempt to base it starting with the second row... Example... A:A (Highlighted) click on CF enter rule Formula =Countif($A2,REFERENCE). Now with reference being a variable that they can select, the conditional formatting will always be a record off because they started their point of reference down by 1. so highlight the area that you are working with, Go to your Conditional formatting and select manage rules and just verify that these are not the case... from there, I can't think of a reason as to why it would be highlighting cells that are not meeting the criteria if you are utilizing FDibbins formula already.

Hopefully you can get a sample book to us soon so we can take a peak and try to source the issue

[Ross85]

Thanks. I will get one up today.

[FDibbins]

Another approach I use when a CF is not working (or is very complex), is to put the formula IN the worksheet, in a blank column. You need to formulate it to return TRUE or FALSE, but now you can play with it a lot easier than in CF.

So, for instance, assuming your data range is A2:A50, in B2 (?) put this in and copy down...
=countif($A$2:$A$50,A2)>1

This should give you a list of TRUE (for cuplicates) and False (for uniques) If it is not doing that where you know it should be fininding either 1 of those, check your data for trailing/leading spaces or other errors

[Ross85]

Got it working. Thanks for all the help guys..

[FDibbins]

Would you care to share your solution with us, in case other members may have encountered this same problem

[Ross85]

Yes,

What I done was select my first cell, as the formula cell in my range. So basic, I just couldn't see it.

Can I ask if it's possible now that that's sorted. If you can get a written response. For example, cell B10 has been highlighted as a duplicate. Can I now get cell C10 to say 51 Para ?

Is this possible ?