Hi MVPs

i currently working on a list which contains several data horizontally, i want to make the data vertically, like transpose copy and paste. however there are over 1000 rows, and the data is not always same length like this :
description material 1 no material 2 no material 3 no material 4 no
Car Maintenance Lubricant 1 Tire 4 Paint Polish 1 Soap 1
Bicycle Maintenance Chain Lube 1 Tire 2 blank blank blank blank

and so on, i want to make it like this :
description material no
Car Maintenance Lubricant 1
Blank Tire 4
Blank Paint Polish 1
Blank Soap 1
Bicycle Maintenance Chain Lube 1
blank Tire 2

and so on. it's easy to make it by using transpose copy and paste, however, i think it must insert number of rows first, then trasnpose paste it. But with over 1000 rows, it will take times, maybe there are some VBA codes to make it easier

Thank you for your help