On Sunday, October 28, 2012 1:56:54 PM UTC-5, 1907rauf wrote:
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









--

1907rauf

Send this and your file to dguillett @gmail.com