Reformatting data

Hello, i want to reformat a file from the data being listed in rows to column with the same order, i have been manually copying and pasting, but that will not work now that the file is so large. There are over 2000 entries that need to be moved around. For example, i have:

Item1 A B C D
Item2 1 2 3 4
etc.

And i want:

Item1 A
Item1 B
Item1 C
Item1 D
Item2 1
Item2 2
Item2 3
Item2 4
etc.

How do i do this. I was trying to do the copy-paste special-transpose, but that wasn't working. What do i need to do this?

hi Jchhoule, this may not be the best method available but it beats manual work i guess. All u have to do is fill in those blue highlighted cells i did. the rest of the formula can be copied from the blue cells. For eg,copy A1 & u get A2 formula. Copy H7:I7 & u get H8:I8.

Hope this helps.

Thanks, but that doesn't really work. Not all items have four extras with them, some have 1 extra while others have 20. I attached an example file, in sheet 1 is what i have in sheet 2 is what i need. I need the item number in the item code column (sheet 1 column C) to be in column A in sheet 2. And everything in the same row in sheet 1 to be next to the master item code on sheet 2 in column B. Thanks for any help!

Thank you, I tried the code you sent and it half worked. It did everything perfectly except when running it would run into a mismatch error. I cannot figure out where the error is coming from because everything seems to be correct. This time I am uploading the shortest list than needs to be arranged. I ran the macro and left everything there so someone could look at it and tell me what is wrong. I appreciate the help.