Hi,

My raw data looks like the following:
A B C D E
2000 76 67 56 46 36
2001 75 65 55 45 35
2002 72 63 57 47 33
2003 74 66 58 48
2004 72 68 52 44
Table 1



I am trying to organize it so it looks like this

Year Company Current Assets
2000 A 76
2001 A 75
2002 A 72
2003 A 74
2004 A 72
2000 B 67
2001 B 65
2002 B 63
2003 B 66
2004 B 68
2000 C 56
2001 C 55
2002 C 57
2003 C 58
2004 C 52
2000 D 46
2001 D 45
2002 D 47
2003 D 48
2004 D 44
2000 E 36
2001 E 35
2002 E 33
2003 E
2004 E
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 E in Table 2 above.

If I haven't been able to explain myself I can send you an excel file in which I have organized one variable in my own time consuming way.

Thanks for the help,

Rauf