cut the cells to 3 columns in excel & remaining columns should be added to the next row

Hi All,

I have an Excel sheet with the following columns and data below:


Adilabad WAVE 3 Agra WAVE 3 Ahmedabad WAVE 3
89 28 97 32 155 56
100 100 100 100 100 100
79 32 98 100 0 0
97 100 0 0 0 0
98 93 0 0 0 0
65 75 2 0 0 0
100 100 100 100 99 98


I have such 40 columns.My requirement is the sheet has to select only 2 columns and then the next coulmns have to be added on to the next rows and so on...
The point is only 2 columns.
If I use TRANSPOSE fuction it swaps the data aswell.But I don't want that...

Below is the output required:

Adilabad WAVE 3
89 28
100 100
79 32
97 100
98 93
65 75
100 100
Agra WAVE 3
97 32
100 100
98 100
0 0
0 0
2 0
100 100
Ahmedabad WAVE 3
155 56
100 100
0 0
0 0
0 0
0 0
99 98


Could anyone please help me on this.

Use this

Please Login or Register  to view this content.

Hi Abdul,

Thank You for the reply.
Could you please clarify where shall I copy this code ???

1.Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
2.Right-click on the Sheet1(Sheet1), then click view code and paste the code
3.Press F5

Hi Abdul,

Thank you.
Its working fine.But for 2 columns......
Its not working for 3 columns...

In the first post you mentioned that only for 2 columns...
Will your data change each time?...So that I can modify the code...


Abdul-

Sorry Abdul,
That was just an example....I thought of modifying the code according to my specifications.
Unfortunately it has thrown some errors when I tried to modify the code.

Actually it was for 3 columns...

Use this...U can use this for any no.of columns

Please Login or Register  to view this content.

Its working.......
U r genious.....

Thank you so much......

...You are most welcome...Please close this thread as SOLVED...


Abdul-