Transpose data in numerous sets

[siddiquera]

My data chart that I downloaded from I/B/E/S looks like this. In order to work with the data I need it to look like the example shown at the bottom. I tried using transpose but I have sets of data which revolve around specific data, to add to that I have over 2000 rows of similar data. ARE IN TWO DIFFERENT SHEETS.

THIS CHART TO LOOKS LIKE THIS.
EventDate Tic Price Returns
18/1/2001 ASBC 31.31250 -0.023438
18/1/2001 ASBC 30.93750 -0.010000
18/1/2001 ASBC 31.56250 0.018182
18/1/2001 ASBC 32.25000 0.023810
18/1/2001 ASBC 32.25000 -0.003876
19/4/2001 ASBC 32.68000 -0.013030
19/4/2001 ASBC 32.41000 -0.004912
19/4/2001 ASBC 32.80000 0.011416
19/4/2001 ASBC 33.29000 0.020439

I NEED IT TO LOOK LIKE THIS
Date PriceT-1 PriceT PriceT+1 PriceT+2 PriceT+3
18/1/2001 31.31250 30.93750 31.56250 32.25000 32.25000
19/4/2001 32.68000 32.41000 32.80000 33.29000 33.03000

 

[hiker95]

siddiquera,

Can we have screenshots of both worksheets, and the resulting worksheet?

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

 

[tusharm]

The OP *has* included samples of what is and what should be!

A picture might be worth a thousand words but in this case four score and seven apparently suffice. {grin}

siddiquera,

Can we have screenshots of both worksheets, and the resulting worksheet?

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

 

[tusharm]

Lightly tested...
Suppose your data are in col. B:E starting with row 1 being the header row.
Suppose you want the result starting with G2.
Then, in G2 enter the formula =INDEX($B$2:$B$10,(ROW()-ROW($B$2))*5+1)
In H2 enter the formula =INDEX($D$2:$D$10,(ROW()-ROW($B$2))*5+(COLUMN()-COLUMN($H$2))+1)
Copy H2 to I2:L2.
Copy G2:L2 as far down until you get a #REF! error.

My data chart that I downloaded from I/B/E/S looks like this. In order to work with the data I need it to look like the example shown at the bottom. I tried using transpose but I have sets of data which revolve around specific data, to add to that I have over 2000 rows of similar data. ARE IN TWO DIFFERENT SHEETS.

THIS CHART TO LOOKS LIKE THIS.
EventDate Tic Price Returns
18/1/2001 ASBC 31.31250 -0.023438
18/1/2001 ASBC 30.93750 -0.010000
18/1/2001 ASBC 31.56250 0.018182
18/1/2001 ASBC 32.25000 0.023810
18/1/2001 ASBC 32.25000 -0.003876
19/4/2001 ASBC 32.68000 -0.013030
19/4/2001 ASBC 32.41000 -0.004912
19/4/2001 ASBC 32.80000 0.011416
19/4/2001 ASBC 33.29000 0.020439

I NEED IT TO LOOK LIKE THIS
Date PriceT-1 PriceT PriceT+1 PriceT+2 PriceT+3
18/1/2001 31.31250 30.93750 31.56250 32.25000 32.25000
19/4/2001 32.68000 32.41000 32.80000 33.29000 33.03000

 

[siddiquera]

Oh my god!! It worked!! Thank You!!!

 

[tusharm]

Why shouldn't it?

I did write "lightly tested." {grin}

Oh my god!! It worked!! Thank You!!!

 

[siddiquera]

Your formula worked brilliantly, but I have a new issue that has popped up, I was wondering if you could help me with this one as well? Your like my go to guy!

I want to move a specific number which represent one ticker one sheet to a different sheet which has the same ticker.

For Example this is the current file:

PERMNO NASDAQ Ticker
15318 19771209 ASBC
59408 19981001 BAC
71563 19990104 BBT
16548 19910424 BOH
76892 19910905 BOKF
85789 19970515 BXS
70519 19981204 C
25129 19721214 CBSH
27888 19970814 CFR
93150 20091210 CIT
25081 19910402 CMA
23916 19900517 CYN
86719 19990208 EWBC


I want to transfer this to a file that looks like this:
PERM Ticker
ASBC 15318
ASBC 15318
ASBC 15318
ASBC 15318
ASBC 15318
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BBT 71563
BBT 71563
BBT 71563
BBT 71563
BBT 71563
BBT 71563
BBT 71563
BBT 71563

There are over 392 variables like this so I could write out a detailed IF function.

How can I solve this issue?

 

[tusharm]

I have no idea how the first becomes the second. What determines the number of rows in which to repeat each data pair?

Your formula worked brilliantly, but I have a new issue that has popped up, I was wondering if you could help me with this one as well? Your like my go to guy!

I want to move a specific number which represent one ticker one sheet to a different sheet which has the same ticker.

For Example this is the current file:

PERMNO NASDAQ Ticker
15318 19771209 ASBC
59408 19981001 BAC
71563 19990104 BBT
16548 19910424 BOH
76892 19910905 BOKF
85789 19970515 BXS
70519 19981204 C
25129 19721214 CBSH
27888 19970814 CFR
93150 20091210 CIT
25081 19910402 CMA
23916 19900517 CYN
86719 19990208 EWBC


I want to transfer this to a file that looks like this:
PERM Ticker
ASBC 15318
ASBC 15318
ASBC 15318
ASBC 15318
ASBC 15318
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BAC 59408
BBT 71563
BBT 71563
BBT 71563
BBT 71563
BBT 71563
BBT 71563
BBT 71563
BBT 71563

There are over 392 variables like this so I could write out a detailed IF function.

How can I solve this issue?

 

[siddiquera]

The ticker would indicate the change in the PERMNO. Where ever the ticker pops up so should the PERMNO. I just need the data to do a lookup and match the data.