Hi,
My raw data looks like the following:[TABLE="width: 500"]
[tr][td][/td][td]A
[/td][td]B
[/td][td]C
[/td][td]D
[/td][td]E
[/td][/tr][tr][td]2000
[/td][td]76
[/td][td][/td][td]56
[/td][td]46
[/td][td]36
[/td][/tr][tr][td]2001
[/td][td]75
[/td][td][/td][td]55
[/td][td]45
[/td][td]35
[/td][/tr][tr][td]2002
[/td][td]72
[/td][td]63
[/td][td]57
[/td][td]47
[/td][td]33
[/td][/tr][tr][td]2003
[/td][td]74
[/td][td]66
[/td][td]58
[/td][td]48
[/td][td]34
[/td][/tr][tr][td]2004
[/td][td]72
[/td][td]68
[/td][td]52
[/td][td]44
[/td][td]38
[/td][/tr]
[/TABLE]
Table 1
I am trying to organize it so it looks like this[TABLE="width: 500"]
Year
[/td][td]Company
[/td][td]Current Assets
[/td][/tr][tr][td]2000
[/td][td]A
[/td][td]76
[/td][/tr][tr][td]2001
[/td][td]A
[/td][td]75
[/td][/tr][tr][td]2002
[/td][td]A
[/td][td]72
[/td][/tr][tr][td]2003
[/td][td]A
[/td][td]74
[/td][/tr][tr][td]2004
[/td][td]A
[/td][td]72
[/td][/tr][tr][td]2000
[/td][td]B
[/td][td][/td][/tr][tr][td]2001
[/td][td]B
[/td][td][/td][/tr][tr][td]2002
[/td][td]B
[/td][td]63
[/td][/tr][tr][td]2003
[/td][td]B
[/td][td]66
[/td][/tr][tr][td]2004
[/td][td]B
[/td][td]68
[/td][/tr][tr][td]2000
[/td][td]C
[/td][td]56
[/td][/tr][tr][td]2001
[/td][td]C
[/td][td]55
[/td][/tr][tr][td]2002
[/td][td]C
[/td][td]57
[/td][/tr][tr][td]2003
[/td][td]C
[/td][td]58
[/td][/tr][tr][td]2004
[/td][td]C
[/td][td]52
[/td][/tr][tr][td]2000
[/td][td]D
[/td][td]46
[/td][/tr][tr][td]2001
[/td][td]D
[/td][td]45
[/td][/tr][tr][td]2002
[/td][td]D
[/td][td]47
[/td][/tr][tr][td]2003
[/td][td]D
[/td][td]48
[/td][/tr][tr][td]2004
[/td][td]D
[/td][td]44
[/td][/tr][tr][td]2000
[/td][td]E
[/td][td]36
[/td][/tr][tr][td]2001
[/td][td]E
[/td][td]35
[/td][/tr][tr][td]2002
[/td][td]E
[/td][td]33
[/td][/tr][tr][td]2003
[/td][td]E
[/td][td]34
[/td][/tr][tr][td]2004
[/td][td]E
[/td][td]38
[/td][/tr]
[/TABLE]
Table 2
I've been able to move all columns with numerical data (Current assets) into one column using the following formula which I found on this forum
=INDEX($A$2:$D$25,MOD(ROWS(F$2:F2)-1,ROWS($A$2:$D$25))+1,INT((ROWS(F$2:F2)-1)/ROWS($A$2:$D$25))+1)
http://www.ozgrid.com/forum/showthread.php?t=83170
however, I have not found a way to efficiently organize the corresponding Years and Company names as in Table 2.
I am currently manually filling in the company names and dragging them each to autofill and Copy pasting the years.
My data consists of about 520 companies across 15 years, and I have 15 variables which I have to re-organize as shown above. There are a lot of gaps in the data but regardless, I want to stack the full 15 years for each company as in Company B in Table 2 above.
If I haven't been able to explain myself please look at the attachment in which I have organized a small part of my data. The entire thing is too big for the attachment.
Thanks for the help,
Rauf