I agree with Marvin here - a macro would be a simpler solution. But, it you want to persevere with formulae you could put this formula in K2 of Sheet1:

=IF(D2="","",D2&"; ")&IF(E2="","",E2&"; ")&IF(F2="","",F2&"; ")&IF(G2="","",G2&"; ")&IF(H2="","",H2&"; ")&IF(I2="","",I2&"; ")&IF(J2="","",J2&"; ")

This will give you what you want, but there will be a semicolon at the end of the string. If you don't want this then you could use this formula in L2:

=IF(K2="","",LEFT(K2,LEN(K2)-2))

Hope this helps.

Pete