Hi,
I have got a sheet with a large number of values such as below where H stands for heading. (I have used to "-" to put the data in proper lines)

H1-------H2---H3---H4
Data1----X
Data2----X
Data3----X
Data1---------X
Data2---------X
Data3---------X
Data1-------------X
Data2-------------X
Data3-------------X

I want to consolidate the data as below.
H1-------H2---H3---H4
Data1----X---X---X
Data2----X---X---X
Data3----X---X---X

Any idea? Any help at all will be very useful. I have no idea about Vb scripting. I have attached a sample file to illustrate my problem.

Thanks,
Nayak

On Wednesday, September 5, 2012 8:03:01 AM UTC-5, jnayak wrote:
Hi,

I have got a sheet with a large number of values such as below where H

stands for heading. (I have used to "-" to put the data in proper

lines)



H1-------H2---H3---H4

Data1----X

Data2----X

Data3----X

Data1---------X

Data2---------X

Data3---------X

Data1-------------X

Data2-------------X

Data3-------------X



I want to consolidate the data as below.

H1-------H2---H3---H4

Data1----X---X---X

Data2----X---X---X

Data3----X---X---X



Any idea? Any help at all will be very useful. I have no idea about Vb

scripting. I have attached a sample file to illustrate my problem.



Thanks,

Nayak





+-------------------------------------------------------------------+

|Filename: Test_Volatility.zip |

|Download: http://www.excelbanter.com/attachment.php?attachmentid=568|

+-------------------------------------------------------------------+







--

jnayak
I would suggest developing a macro that would sort the data and then line up as desired from the bottom up using a loop

I have got a sheet with a large number of values such as below where H
stands for heading.

H1-------H2---H3---H4
Data1----X
Data2----X
Data3----X
Data1---------X
Data2---------X
Data3---------X
Data1-------------X
Data2-------------X
Data3-------------X

I want to consolidate the data as below.

H1-------H2---H3---H4
Data1----X---X---X
Data2----X---X---X
Data3----X---X---X

With the original data in Sheet1, one way is to put all the following in Sheet2.

In A1 put
=Sheet1!A1
and copy rightward to D1.

In A2, put
=INDEX(Sheet1!$A:$Z,MOD(ROW()-2,3)+2,1)
and copy downward to A4.

In B2 put
=INDEX(Sheet1!$A:$Z,
MOD(ROW()-2,3)+2+3*(COLUMN()-2),
COLUMN())
and copy to all of B2:D4.

If there are more headings, extend the Sheet2 columns farther to the right.

Hope this helps getting started.