Need a Formula

[TnMike]

I have a list of MAC address formated like: xx:xx:xx:xx:xx:xx. This list
also contains the customers address. The MAC address is in column A and
address in column B.

I need a formula or a macro that will sort the MAC address along with
the respective address, then show me the ones that have been entered
wrong (to few or to many numbers), and are doubled (MAC addresses that
have been entered twice.
I can upload a sample (with no real numbers) if need be.

Thanks in advanced for your help!

 

[Guest]

Hello TnMike,

Seeing as no-one else has had a go I'll take a bash!

The LEN function combined with the IF will alert you if your MAC addresses
are not 17 characters long:

=IF(LEN(A1)=17,"Error","")

The COUNTIF function will alert if there are copies:

=IF(COUNTIF($A$1:$A$100,A1)>1,"Error","")

Depending on the whether you want one message if either of these variables
are wrong or whether you want seperate messages we could combine the 2
together.

Post back if you are still working on this. I'll keep an eye out. If you
want to send the file: jhall52 AT hotmail DOT com

Judith

 

[TnMike]

Thanks Judith!

I would like to have it show me the MAC along with the address that is
incorrect and show the MAC(Along with the address) that has been
entered twice.
That way, I can deal with the differences on a customer by customer
basis.

Where would I insert the formula?
I will send you an email with a sample of the excel sheet.

Thanks for the help.