Automatically assign circuit numbers. Perplexing, so ALL GURUS WELCOME.

This is an extension of this thread...
http://www.excelforum.com/showthread.php?t=1086927

Used that formula "snippet" to assign certain circuit numbers when a certain condition applied (one space circuit). Where that condition does not apply, I want to assign lowest circuit numbers. On there own, my formula parts work, but because using the formula "snippet" from the other thread skips space numbers, it leaves unused "voids" in consecutive odd or even space numbers. As a result, my "normal" formula either skips a space(s), or it's assignments overlap already used spaces.

Please see attached example file for more detail...

Refer to this picture as a depiction of the physical...

501ecm31fig1.jpg

Each of the larger wires (2 each of black [ØA], orange [ØB], and blue [ØC]) goes to what is called a bus. The breakers are arranged below that, left and right. Each wire coming into a breaker from the respective side represents the same Ø by color. Each of those smaller wires represent a breaker space, numbered odd left, even right, top down.

A circuit can be 1-, 2-, or 3-pole. Each pole of that circuit requires one breaker space. If the circuit is 1-pole (as indicated by CONNECTION column in my Table), it can only be connected to a breaker in a black- or blue- wired space.

I want to automatically number the circuits in my Table giving them top down priority so that it provides the least unused spaces in the breaker panel.

Hope that helps. let me know if you (all) need more explanation.

Top data row in my Table, left-most cell (B4) will always be CKT #1. If it has 1 in [CONNECTION], [##] and [###] columns will be blank. If [CONNECTION] is 2, [##] will be 3 and [###] will be blank. If [CONNECTION] is 3, [##] will be 3 and [###] will be 5. These represent assigned breaker spaces by number.

Second row must use the numbers above in those three columns to determine what breaker spaces are available. There will never be more breaker spaces than the rows(first:current) times columns(first:third), so at this stage, a total max possible spaces is 6. The available spaces should be 2,3,4,5,6, with 3 and 5 availability determined by the row above. The lowest available space number for second row is 2. Depending on what is in [CONNECTION], the left 3 columns will be 2,"","", or 2,4,"" or 2,4,6.

AFAIK, everything is good through second row. Third row (and thereafter) is the earliest the problem starts...

Say for example rows one and two have a [CONNECTION] of 1... those rows would be 1,"","" and 2,"","" respectively. If row three has a [CONNECTION] of 1, it'll skip {3,4} and assign 5.

Or for example rows one and two are 1,3,5 and 2,"","" respectively... row three will skip {4} and assign 6.***

Now it is feasible that the number of "required" spaces could exceed rows time columns (3×3) because of the skipping. I have anticipated this, but have yet to modify the formula... working one problem at a time ...and that should be a relatively easy fix.

Any way, because each new row three or later may have skipped spaces above, the latter part of my formula needs to "anticipate" all three columns number assignment so it doesn't duplicate an already used space. Continuing last example (***), row four will want to assign 4 in [CKT#] column if [CONNECTION] is 2 or 3... but it'll assign 6 in [##]... which will duplicate the 6 in row three.

Does this help?

Bump.......

That was the notion when I typed up the thread title...

I guess that someone was me...

Finally got it AFAICT...

First, that's your (also others) method of installing the breakers. It is not the only way to do it.

2-pole breakers can be installed AB, BC, or CA. If you do not install in one of those, it is to help balance the load, but just doing it that way isn't an assurance that it balances the load. basically a make-shift method of balancing without doing a panel load calculation (i.e. the lazy or a hack's way, if you'll pardon the implication). 1-pole breakers can only be on A and C lines (technically inaccurate to call them phases) because those are the only lines with 120V to neutral (the white wires in the diagram of post #3). Line B to neutral is 208V and not intended to be used for 208V loads (I can explain further if you want).

For this discussion, I am using a 240/120V 3Ø 4-wire system. A 480/240V 3Ø 4W system typically does not use 1-pole breakers. And then there's 208/120V and 480/277 3Ø 4W systems, but 1-pole breakers on those systems can be installed in any space.

Anyway, you're only seeing part of the operation if you opened either of the example files. The actual file includes the panel load calculation per line (conventional and vector) and offers balancing by manually rearranging the rows (it would take a VBA routine to balance it automatically, and judging by writing this numbering formula, I'm imagining that would be an even more daunting task ). Anyway, that's why I wanted automated circuit numbering. Balancing the load takes a higher priority than just putting in breakers where they fit best (at least it does for the true professional).

Regardless, I appreciate both your interest and well wishes...