How to copy data from non consecutive cells and paste them in another sheet as consecutiv

Hi there, hoe gaan did daar in dir Kaap? (SA ex-pat here from PE, Carltonville and Klerksdorp)

This is probably not what you want, but give it a try anyway - it will give you all classes in 1 cell...
=SUBSTITUTE(TRIM(Sheet1!X2&" "&Sheet1!Y2&" "&Sheet1!Z2&" "&Sheet1!AA2&" "&Sheet1!AB2&" "&Sheet1!AC2&" "&Sheet1!AD2&" "&Sheet1!AE2&" "&Sheet1!AF2&" "&Sheet1!AG2&" "&Sheet1!AH2&" "&Sheet1!AI2&" "&Sheet1!AJ2&" "&Sheet1!AK2)," ",",")
copied down

I will do some more work on this to break them out into individual cells

If all names are in same order.. then try this..

=IFERROR(INDEX(Sheet1!$E$1:$R$1,SMALL(IF(LEN(Sheet1!$E2:$R2)>0,COLUMN(Sheet1!$E2:$R2)+1-MIN(COLUMN(Sheet1!$E2:$R2))),COLUMN(A1))),"")

With Ctrl + SHift + Enter.

COURSE SELECTION GR9 -2014.xlsx

Nice 1 Debraj

I came up with this monster to concat them on sheet1
=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(X2&"@"&Y2&"@"&Z2&"@"&AA2&"@"&AB2&"@"&AC2&"@"&AD2&"@"&AE2&"@"&AF2&"@"&AG2&"@"&AH2&"@"&AI2&"@"&AJ2&"@"&AK2,"@",",")," ","^"),","," "))," ",","),"^"," ")

And then this to break that concat apart...
=TRIM(MID(SUBSTITUTE(Sheet1!$AL2,",",REPT(" ",LEN(Sheet1!$AL2))),LEN(Sheet1!$AL2)*(COLUMN()-7)+1,LEN(Sheet1!$AL2)))

Yours is streets better, well done