Transposing Data with Key Field Having Multiple Associated Values In a Row

Question

I have a key column with ID numbers and for each ID number in the rows, there may be up to 5 values in different data columns so it looks like this:

ID   Unrelated Column   Unrelated Column   Data1   Data2  Data3  Data4  Data5
001  Ignore this data   Ignore this Data   red     blue                     
002  Ignore this Data   Ignore this Data   yellow  green  orange red
003  Ignore this Data   Ignore this Data    
004  Ignore this Data   Ignore this Data   purple  blue   black

What I would like to do is have formulas in another worksheet that present this data in a different way to look like this:

ID        Data
001       red
001       blue
002       yellow
002       green
002       orange
002       red
004       purple
004       blue
004       black

Thank you,

Lindsay

Answer 1

Apply the solution here, then delete the rows that are blank for 'Value' and those "Unrelated Column" for 'Column' and delete 'Column'.