Hello,
I'm hoping someone can help me out. I have devices with 2 MAC addresses. The 2nd MAC addresses always increment +1. I want to verify that the MAC addresses follow that logic.
I've attached an example of what I am working with in hopes that someone can find a solution.
Thanks!
Last edited by redfusion3; 07-01-2014 at 02:02 PM.
=IF(CODE(RIGHT(C2))-CODE(RIGHT(B2))=1,"Yes","No")
I'm not quite sure how you wish to handle 9 to 10 or Z to A conversions, but this covers everything else.
Make Mom proud: Add to my reputation if I helped out!
Make the Moderators happy: Mark the Thread as Solved if your question was answered!
Thank you for the formula. Unfortunately, its only gets me halfway there.
Since MACs are always 12 characters in length and use 0 - 9 and A - F, I am getting problems from the jump from 9 to A.
See example:
Address 1 Address 2 Increment correct?
CC3540C4D082 CC3540C4D083 Yes
C427954B5919 C427954B591A No
001DD1385E92 001DD1385E93 Yes
001DD23977F2 001DD23977F3 Yes
001DD243D122 001DD243D123 Yes
001DD45E7F62 001DD45E7F63 Yes
001DD420E702 001DD420E703 Yes
001DD5070FC2 001DD5070FC3 Yes
CCA462013C72 CCA462013C73 Yes
Thanks.
I'm kind of techie dumb, despite my title.
How does one properly count from A to 0 and 9 to A.
Edit:
Ok, I'm not technically that dumb.
=IF(HEX2DEC(RIGHT(B1))-HEX2DEC(RIGHT(A1))=1,"Yes","No")
Last edited by daffodil11; 07-01-2014 at 03:51 PM.
No, you're not dumb at all. That did the trick.
Thank you very much sir!
Sample.xlsx
I used your formula on all the data I have and it pointed out that it doesn't account for the jump of F back to 0 transition. Sorry, I should have mentioned that.
Is there anyway you can take another look?
I've attached a data sample if you can think of anything else. Everything in this sheet should pass the logic. As you can see, the last two examples fail.
Thanks.
Just extend the sampling. This method won't reliably take HEX values greater than 9 characters, due to Excel's precision issues.
=IF(HEX2DEC(RIGHT(C2,9))-HEX2DEC(RIGHT(B2,9))=1,"Yes","No")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks