Transform Horizontal data to vertical Column

Posted on 2011-04-30
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
    LVL 43

    Accepted Solution

    Without VBA you can use something like this formula


    as shown in the file below

    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    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

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    The purpose of this article is to demonstrate how we can use conditional statements using Python.
    Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
    The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now