Transpose Multiple Columns to one Row with Unique Identifier

I am trying to transpose A LOT of data (>10,000 unique identifiers) from multiple colums into rows with their unique ideitifiers.

Here is a sample of my data:

ID category1 category2 category3
10 walk run
11 run walk jump
12 sit jump
13 jump

This is what the result would be:
ID Category
10 walk
10 run
11 run
11 walk
11 jump
12 sit
12 jump
13 jump

Please notice that all IDs do not have the same number of categories. In my actual data, some of my IDs have up to 19 categories. Does anyone have a macro they could share?

Thank you SO very much!

This seems to work for your example data:

Thanks for the response. However, no data shows up in the highlighted cells that contain formulas you provided. Am I missing something?

They show up in my version, the "iferror" function should work in Excel 2007 and higher.

Try

Please Login or Register  to view this content.

Sample.xlsm