Hi,
My raw data looks like the following:
Table 1
A B C D E 2000 76 56 46 36 2001 75 55 45 35 2002 72 63 57 47 33 2003 74 66 58 48 34 2004 72 68 52 44 38
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
2001 B
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 34
2004 E 38
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
Bookmarks