multiple columns into one column and autofill(?) question

[1907rauf]

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 th
corresponding Years and Company names as in Table 2.

I am currently manually filling in the company names and dragging the
each to autofill and Copy pasting the years.

My data consists of about 520 companies across 15 years, and I have 1
variables which I have to re-organize as shown above. There are a lot o
gaps in the data but regardless, I want to stack the full 15 years fo
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 i
which I have organized one variable in my own time consuming way.

Thanks for the help,

Rau

 

[Don Guillett]

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

Send this and your file to dguillett @gmail.com