consolidation data in one row

[jnayak]

Hi,
I have got a sheet with a large number of values such as below where
stands for heading. (I have used to "-" to put the data in prope
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 V
scripting. I have attached a sample file to illustrate my problem.

Thanks,
Naya

+-------------------------------------------------------------------
|Filename: Test_Volatility.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=568
+-------------------------------------------------------------------

 

[Don Guillett]

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|

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

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

 

[zvkmpw]

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 B24.

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

Hope this helps getting started.