Auto-increment across rows and columns

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

Wow, amazing! Actually, the values should be numbers, not codes/text. That was an error on my part before I posted the screenshot. I was never able to get your last formula to work with the cells formatted as text.

Thanks so much! You literally saved me hours, if not days, on this project alone. I should have asked for help earlier!

Happy to help, thank you for the rep!
PS For the codes formula, format the cells as General. The OUTPUT itself is text.