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!

[JudithJubilee]

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
--
Hope this helps


"TnMike" wrote:

>
> 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!
>
>
> --
> TnMike
> ------------------------------------------------------------------------
> TnMike's Profile: http://www.excelforum.com/member.php...o&userid=33916
> View this thread: http://www.excelforum.com/showthread...hreadid=536947
>
>

[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.