+ Reply to Thread
Results 1 to 2 of 2

multiple columns into one column and autofill(?) question

  1. #1
    Registered User
    Join Date
    10-28-2012
    Location
    Sweden
    MS-Off Ver
    Excel 2011 mac
    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 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
    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
    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
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: multiple columns into one column and autofill(?) question

    In the attached file I have shown you how to do it by setting up 3 formulae in A2, B2 and C2 of Sheet1. These can be copied down until you see blanks in column A (i.e. no more data). I've copied to row 85, to be sure.

    Hope this helps.

    Pete
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1