1

My data looks like this 

2010 2011 2012 2013 2014 
A     B     G   H    J
J     G     R   J    K

I want it to look like this 

2010  A
2011  B
2012  G
2013  H
2014  J
2010  J
2011  G
2012  R
2013  J 
2014  K
CallumDA
  • 12,025
  • 6
  • 30
  • 52
Clark
  • 11
  • 1
  • 1
    See here: http://stackoverflow.com/questions/20541905/convert-matrix-to-3-column-table-reverse-pivot-unpivot-flatten-normal You will need to add a garbage column to the left of your data that you delete after ward. – Scott Craner Mar 15 '17 at 15:36
  • Do you really want to do this with VBA? – CallumDA Mar 15 '17 at 16:07

2 Answers2

1

1) copy row 1 and insert between 2 and 3, you will get:

2010 2011 2012 2013 2014 
  A     B     G   H    J
2010 2011 2012 2013 2014 
  J     G     R   J    K

2) copy row 3 & 4 and paste behind data in line 1/2, you will get:

2010 2011 2012 2013 2014  2010 2011 2012 2013 2014 
  A     B     G   H    J    J    G    R    J    K

3) copy all data, paste with transponation and you will get what you want

Enrico
  • 148
  • 11
0

You can use array formula. When a1:a3 is 2010, A, J, let's put output at g:h.

First, select g1:g5 and hit F2 and type =TRANSPOSE(A1:E1) and hit ctrl + shift + enter. Now g1:g5 should be 2010, 2011, ..., 2014.

Select g6:g10 and hit F2 and type =TRANSPOSE(A1:E1) and hit ctrl + shift + enter. Now g1:g5 should be 2010, 2011, ..., 2014.

select h1:h5 and hit F2 and type =TRANSPOSE(A2:E2) and hit ctrl + shift + enter. Now h1:h5 should be A, B, ..., J.

Select h6:h10 and hit F2 and type =TRANSPOSE(A3:E3) and hit ctrl + shift + enter. Now g1:g5 should be J, G, ..., K.

enter image description here

Sangbok Lee
  • 2,132
  • 3
  • 15
  • 33