Take Multiple Instances from a Row and Move to a Column

[HangMan]

I have a sheet with the following sample layout (sample attached). The actual sheets have around 8,000 rows each and can have varying numbers of column entries per code, i.e. there could be three as per the sample attached, Country, Animal, Cost or there could be say six Country, Region, Colour, Animal, Cost, Ship.

Code.........Country1.......Animal1.......Cost1...............Country2........Animal2......Cost2...........Country3.................Animal3.........Cost3
BZ1234.......Africa............Buffalo.........£10,000.00..........China..............Jaguar.........£752.00.........The Netherlands.........Otter.............£568,346.00
BZ3658.......America.........Cat..............£3,456.00...........Canada...........Tiger............£34,658.00
BZ7637.......England.........Dingo............£7,634.00...........Austria............Hog............£34,678.00
BZ5034.......Fance...........Rabbit...........£890.00


I need to be able to run a macro that looks across each row in the sheet and extracts the data onto a new sheet putting all the countries into a single column, all the animals into a single column and all the costs into a single column and duplicates the code for each itteration within the row, resulting in:

Code.........Country..............Animal......Cost
BZ1234......Africa..................Buffalo.......10,000.00
BZ1234......China..................Jaguar........752.00
BZ1234......The Netherlands....Otter.........568,346.00
BZ3658......America...............Cat............3,456.00
BZ3658......Canada...............Tiger..........34,658.00
BZ7637......England...............Dingo.........7,634.00
BZ7637......Austria................Hog...........34,678.00
BZ5034......Fance .................Rabbit........890.00

The macro would need to take account of the fact that the number of columns for associated entries could vary, so it would be helpful if this could somehow be determined through a user entry box. So in this example there are three associated columns for each entry, but this could be any number, so it would need that flexibility. A User entry box would allow the user to enter say, 3 or 6 based on the number of columns per code.

Is this possible to achieve?

Many thanks