vba - excel macros for pasting multiple columns data under one column -

in excel data in below format,

but need data in below format,

can me in creating macros it.

i used below macro not working,

sub combinecolumns1() 'updateby extendoffice 20151030         dim xrng range         dim i, j integer         dim xlastrow integer         dim xtxt string         on error resume next         xtxt = application.activewindow.rangeselection.address         set xrng = application.inputbox("please select data range", "kutools excel", xtxt, , , , , 8)         if xrng nothing exit sub         xlastrow = xrng.columns(1).rows.count + 1         = 4 xrng.columns.count                 j = 1 3                         range(xrng.cells(j, i), xrng.cells(xrng.columns(i).rows.count, i)).cut                         activesheet.paste destination:=xrng.cells(xlastrow, 1)                         xlastrow = xlastrow + xrng.columns(i).rows.count                 next                 j = 1         = + 2         next end sub 

for formula put original data in rows 1 , 2. in row 4 put 3 headers.

then in a5 put formula:

=index($2:$2,((row(1:1) - 1) * 3) + 1 + (column(a:a)-1)) 

then drag/filled on 2 columns , down 2 rows.

this formula uses entire second row reference data no matter how many columns included , needs drag/filled down sufficient number of rows.

if data pattern different every 3 columns change 3 number of columns in pattern.

---

as per comments:

=iferror(index($2:$4,int((row(1:1)-1)/(match("zzz",$1:$1)/3))+1,(mod((row(1:1)-1),match("zzz",$1:$1)/3) *3)+1 + (column(a:a)-1)),"") 

---

to go rows first columns flip 2 row references:

=index($2:$4,mod(row(1:1)-1,3)+1,int((row(1:1)-1)/3)*3+1+column(a:a)-1)