CODEWITHSUNDEEP
excelvba
ic3
ic3

Reputation: 7680

Excel Table to List

I'm looking for a plugin or a Macro to convert in Excel a table to a List. I could develop a VB code for this but it looks as a problem other people might already have solved.

A simplified verison of the problem is :

Source

-       -        -       2010 2009 
Spain  Male  Population   54    53
Spain  Femal Population   64    63
Spain  Male  PIB          2     3

Result

Spain  Male  Population  2010 54 
Spain  Male  Population  2009 53 
Spain  Femal Population  2010 64
Spain  Femal Population  2010 63
...

Help is appreciated.

Upvotes: 0

Views: 1513

Answers (1)

brettdj
brettdj

Reputation: 55672

You can re-order your data with several OFFSET formulae

If your data was in A:E

  • in G1 put =OFFSET(A$2,(ROW()-1)/2,0) and copy to colulm I and down
  • In H1 put `=OFFSET($D$1,,MOD(ROW()+1,2)) and copy down
  • in I1 put=OFFSET($D$2,INT(ROW()-1)/2,MOD(ROW()+1,2))

This can be run in VBA if you like

enter image description here

Upvotes: 1

Related Questions