Need help tabulating subnets in 1 row

[npereira]

Hi,

I have a list of subnet 1 per row i need to setup.

I need to know a formula that will change the second octet to be the next (or +1) per row in the same column. see example bellow.

ColumnA ColumnB
site1 10.101.1.0
site2 10.102.1.0
site3 10.103.1.0

The second octet must be the next one. But when I click and drag the bottom right handle, it cycles through 101, 102, 103 then starts again 101,102,103

How can I do this with formulas or how to change the behavior in order to do this?

[Andrew-R]

A simple user-defined function may be the best idea here:

Please Login or Register  to view this content.

With this if your first IP address is in B2 in B3 you can use:

=IncrementIP(B2,2)

Where the second parameter is the octet you wish to increment.

[npereira]

The cycle must be 101, 102, 103, 104, 105 ,106 etc.... but it stops at the last row selected and starts back as 101

[npereira]

where do I put this code?

[Andrew-R]

To use my code ...

1. It will be easiest if you only have the 1 workbook open, so shut down any other workbooks you might have open.

2. Press Alt-F11 to open the VB editor

3. From the insert menu select "Module"

4. A blank editing window will open up in the middle of the screen.

5. Paste my code into that window

6. You can now close the VB editor. If you're using Excel 2007 or later then you'll no longer be able to save the workbook as a ".xlsx" file, and will have to save it as an ".xlsm" file.

7. Try the formula I wrote for you.

8. Let me know if it works

[npereira]

ok, found how to add the module and this works ! Thanks

[npereira]

Hmmm.. how do you save this workbook while keeping the custom module?

[npereira]

Nevermind...

[npereira]

So if I need to jump 2, meaning if i need 10.101.1.0 and the next column needs to be 10.101.3.0 ? This code will only go from 10.101.1.0 to 10.101.2.0... I need to segregate each subnets into /23's.

Thanks

[Andrew-R]

You can updated my original function to this:

Please Login or Register  to view this content.

Which means that you can now call:

=IncrementIP(B2,2,2)

Where the final 2 indicates to skip that number when incrementing. And if you wanted to skip 2 and 3 you could use:

=IncrementIP(B2,2,2,3)

Or 2,3 and 11:

=IncrementIP(B2,2,2,3,11)

And so on.