Validation Warning for repeating a number in a column

Closed
barewirewalker Posts 3 Registration date Tuesday June 18, 2013 Status Member Last seen June 24, 2013 - Jun 18, 2013 at 10:12 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jun 27, 2013 at 11:37 AM
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

7 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Jun 18, 2013 at 10:57 AM
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 Posts 3 Registration date Tuesday June 18, 2013 Status Member Last seen June 24, 2013
Jun 18, 2013 at 12:24 PM
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 Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Jun 20, 2013 at 10:26 AM
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
Jun 21, 2013 at 06:25 AM
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 Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Jun 24, 2013 at 10:40 AM
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 Posts 3 Registration date Tuesday June 18, 2013 Status Member Last seen June 24, 2013
Jun 24, 2013 at 01:59 PM
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 Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Jun 27, 2013 at 11:37 AM
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