[Last Call] Learn how to a build a cloud-first strategyRegister Now


Transform Horizontal data to vertical Column

Posted on 2011-04-30
Medium Priority
Last Modified: 2012-08-13
Hi all

I have a need to re-arrange the data set.. see attached figure..
The data are arranged by Yearly Quartal..
i.e starting from 1901(4 data set); 1902 (4  data set) etc..
I am looking for a formular or a tutoiral that will help me
re-arrange them in columns i.e

Ist Quarter; Second Quarter; Third Quarter; Last Quarter; and Year
1.55946;      1.585132;            1.78814;     1.8174;               1901


Thanks your help will be appericated..

 Transformation Horizontal to vetical
Question by:ZURINET
  • 2
LVL 43

Accepted Solution

Saqib Husain, Syed earned 2000 total points
ID: 35496867
Without VBA you can use something like this formula


as shown in the file below

LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35496888
If you want to have VBA then you can use

Sub transform4()
For Each cel In Sheet1.Columns(1).Cells
If cel.Value = "" Then End
Sheet2.Cells(Int((cel.Row - 1) / 4) + 2, (cel.Row - 1) Mod 4 + 1) = cel.Value
Next cel
End Sub

The results would be on sheet2

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Suggested Courses
Course of the Month18 days, 5 hours left to enroll

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question