Swap rows and columns automatically

[PinotNut]

I have tediously created and populated a spreadsheet that is simple but has hundreds of rows. Rows represent COLORS, columns are PAPER TYPE. I have entered the CATALOG Number for the intersection of Color and Paper Type. It took me a long time to fill in all the Catalog Numbers. Now that I'm finished I would like to create another spreadsheet, but this time with the Catalog Numbers down the left column and the Color inserted for the Paper Types. I don't want to have to manually recreate this spreadsheet. It will take hours more. Is there some kind of Excel function or formula that would automatically swap the Colors with the Catalog numbers keeping the Paper Type columns in the same place? I have posted a sample of my Excel spreadsheet as an example. Thanks in advance for your assistance.

[Glenn Kennedy]

One way, in L3, copied down:

=IFERROR(LOOKUP(2,1/(B3:J3<>""),B3:J3),"")

and in M3, copied across and down:

=IF(B3<>"",$A3,"")

[CK76]

You can use Transpose

Ex:
=TRANSPOSE(Sheet1!A1:J21)&""

If using Excel 2010, follow direction in the link.
https://support.microsoft.com/en-us/...9-4b6dfac76027

If using Office 365, formula can be used as is, and will automatically fill range.

[PinotNut]

Thanks, Glen, but I am posting in the Beginners' forum. I am a rank beginner so I have no idea how to pull off what you suggested. I'm assuming I need to learn how to use formulas.