Thanks for the response,I already tried out this method probably its my fault I didnt told what I have tried out already.
a)I have tried the transpose option also.
I do not want this drag drop or any dummy value in the first 4 columns as this sheet data will be uploaded to other system in the subsequent stages.
Hence I do no want any dummy value and I know the the backend processing which I think can be done u using the macros.
Hope I am clear,if not plz get back,thanks once again....