Home |
Search |
Today's Posts |
#1
|
|||
|
|||
multiple columns into one column and autofill(?) question
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple columns into one column and autofill(?) question
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Applying autofill macro that covers multiple sheets/columns | Excel Programming | |||
column autofill question | Excel Worksheet Functions | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
Q. Autofill question: Can I autofill alpha characters like I can numbers? | Excel Programming |