Validation Warning for repeating a number in a columnClosed

Question

Hello,

I am using a spreadsheet that compares properties, within different grid squares. The column for grid references has numbers prefixed with two letters,eg. SV1243.

Can I use a formula to warn me if I have already entered a reference, the same as being typed in and what would it be?

Regards

TrowaD · Answer

Hi Barewirewalker,

You could use an array formula.

Let's say your references are in column A, then your formula would look like:
=SUM(IF($A$2:$A$100=A2;1;0))
Array formula's need to be confirmed by hitting Ctrl+Shift+Enter.

You can now drag the formula down as far as needed.
Also adjust the range A2:A100 to the range you might be using.

The formula will count the number of times a reference is used, so you can also apply conditional format to cells bigger then 1.

Please let me know if something is unclear.

Best regards,
Trowa

barewirewalker · Answer

Thanks, Trowa, for that prompt reply,  Excel tells me I am using an incorrect formula,
Perhaps I should have mentioned that I am using Excel 2003. 
but I shall continue to persevere with it, and check that I am in the correct cells.

TrowaD · Answer

Hi Barewirewalker,

I'm also using Excel 2003, so the formula should work.

When I take a second look at the formula I notice that I forgot to change the ;'s into ,'s.

So the formula should be:
=SUM(IF($A$2:$A$100=A2,1,0))

Don't forget to confirm using Ctrl+Shift+Enter. If done correctly the formula should be enclosed by curly brackets.

Best regards,
Trowa

barewirewalker · Answer

Thanks again for your interest and help.

I have been able to enter the formula into the Data>Validation>custom>box and I have filled in the appropriate messages for a warning, but I am not getting a reaction/warning when I enter a same reference eg. SJ4544, and SJ4544.

I am not very savvy with Excel, as I have not had to use it very much so I may well be making elementary mistakes. I will continue to persevere.

By "Don't forget to confirm using Ctrl+Shift+Enter" I presume this is alternative to clicking on enter to activate the validation, though I have tried  Ctrl+Shift+Enter as I type in the incorrect Grid Reference.

TrowaD · Answer

Hi Barewirewalker,

Data Validation doesn't accept array formula's.
The formula I have given you needs to be put in a column.

The result will be the number of times a reference is used.
That is why I advised you to apply conditional formatting to clearly see if a reference is used already.
The column used can be hidden if desired.

Will this work for you or do you prefer a macro code.

Best regards,
Trowa

barewirewalker · Answer

Hi Trowa, 
That will explain why, even having managed to enter the formula, I have had no success. It returns the warning for any no. entered.

This also happens with a formula I found here;
https://thesmartmethod.com/search/excel2010/formulas.html 
Excel 2010 Expert Skills Lesson 2-14

But this formula/function has the same effect once entered it does not allow any number to entered. So I presume Excel 2003 does not handle this as well.

Your suggestion of a column enumerating the no. of entries so that there is a visual check makes sense.

Not sure I understand macros yet. I still have an enormous amount of data to process and am looking at .csv files to enter this into the spread sheet, so I will be looking for any improvements to help keep out errors.

As the serial numbers are grid references, I have decided to have a map of the references on a separate sheet in the same Excel document.

Again thanks for your help, it has helped me progress in understanding, which would have been difficult without

TrowaD · Answer

Hi Barewirewalker,

Not sure if you found a workaround or not but the following file contains exactly what you want, right?:
http://www.ge.tt/8b7fFPk/v/0

Also it is nice to understand macro's but it is not necessary for them to work.

Best regards,
Trowa

Validation Warning for repeating a number in a column

7 responses

Similar discussions

Subscribe To Our Newsletter!