Transposing Complex Data into Usable Format

Hi All,

I know there are plenty of super intelligent excel wizards on here so im hoping for some help.

I have a file which has hundreds of site IP & Subnet information and Site ID's that isnt presented in the most helpful way. Im looking to take the information which is in multiple rows/columns and need them transposing into multiple columns but the information is not easy to manipulate (IMHO). I have looked at "transpose" but its not working as i need and im at a loss.

Attached is a spreadsheet with "Current" how it is now" and "How Id Like It To Be".

Hoping someone can help get the information to where i need it to be.

Thanks all
Craig