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

i have key column id numbers , each id number in rows, there may 5 values in different data columns looks this:

id   unrelated column   unrelated column   data1   data2  data3  data4  data5 001  ignore data   ignore data   red     blue                      002  ignore data   ignore data   yellow  green  orange red 003  ignore data   ignore data     004  ignore data   ignore data   purple  blue   black 

what have formulas in worksheet present data in different way this:

id        data 001       red 001       blue 002       yellow 002       green 002       orange 002       red 004       purple 004       blue 004       black 

thank you,

lindsay

apply solution here, delete rows blank 'value' , "unrelated column" 'column' , delete 'column'.