I'm taking a shot in the dark here. I'm fairly certain what I'm trying to do is possible...it just might not be free

I have some formula experience, less VBA experience. I have a file(s) where each row(element id) represents a device. Any given device may have 1, 2, 4, 6, or 8 cables, indicated by the GP_DropQuantity column. The 'CableID#' columns represent the cable #s for each device. These cable numbers need to increment sequentially so that there are no duplicates (and the sum of the drop quantity column equals the highest cableid value.

It would be extremely helpful to be able to auto-increment the cableid values so that a change in one increments all subsequent cells.

Or, to think of it another way, I've been wondering if there was a way to 'transpose' all the cableid values to one column, fill the series, and then return back to individual cableid columns. But I fear this would require introducing another column value to keep the devices in a specified order.

Any ideas much appreciated. Thanks.

Capture.PNG