+ Reply to Thread
Results 1 to 7 of 7

Converting a matrix into multiple row format

  1. #1
    Registered User
    Join Date
    02-11-2012
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    7

    Converting a matrix into multiple row format

    Hello,

    I have data set-up in a matrix. There is a single row for each position # (4,332 records) in column A. In the next 134 columns (B:EE), there is a marking designating an assignment to that position #. There are multiple assignments to each position number and these number of assignments per position number varies. I need to convert this into a format in which there are multiple rows per position # (number of rows per position equal to the number of assignments it has) and then in the adjacent column I need each of the roles for that position listed. In other words, I have this:

    Column A Column B Column C Column D Column E
    Position 1 Assgn 1 Assgn 3 Assgn 4
    Position 2 Assgn 2 Assgn 4

    And I want it to look like this:

    Column A Column B
    Position 1 Assgn 1
    Position 1 Assgn 3
    Position 1 Assgn 4
    Position 2 Asgn 2
    Position 2 Assgn 4


    I have attached an Excel file with the data. SHeet 1 contains the mapping data. Sheet 2 contains a sample of the first two positions in the format that I need to convert it to.

    I would REALLY appreciate any help on this. I really appreciate any guidance or example code here. Thank you!!!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Converting a matrix into multiple row format

    Output in sheet3 (Button in sheet3)

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by AB33; 04-06-2013 at 06:06 AM.

  3. #3
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Converting a matrix into multiple row format

    I have just notice on your sample output,that you picked up the first and third rows as output not rows 1 and 2. I am not sure if this is just a sample, or you want to do it every other row, i.e. rows 1,3,5 and 7. If this is your intention, I would modify the above code.

  4. #4
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Converting a matrix into multiple row format

    I have not seen the file, but from your thread requirement try this:-
    Data assumed to start row(2)
    Results on sheet2 start s row(2)
    Please Login or Register  to view this content.
    Regards Mick
    Last edited by MickG; 04-05-2013 at 12:55 PM.

  5. #5
    Registered User
    Join Date
    02-11-2012
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Converting a matrix into multiple row format

    Thank you all for you very timely help.

    AB33 - I do not need the data in multiple row format. Sorry my text in forum post did not match the example in my spreadsheet. When I ran your code, I get a "out-of-memory" error. I tried shutting down computer and running the code with no other programs open. Is there a way for me to fix this?

    MickG - this code worked - thank you so much for your help. There were a couple of issues though: the code pastes the entire row into transposed format, including blanks. As a result, the output in sheet 2 is 500,000 rows (without blanks it is around 50000). This should be an easy filter/delete but my computer can't handle deleting that many rows. Is there a way to exclude blanks? Also, it converts the paste to general format which changes all those position numbers beginning with 0's (e.g. 00000003 becomes 3). Is there a quick way to paste as text in code?

    Both of you have been very helpful. Thank you.

  6. #6
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Converting a matrix into multiple row format

    Flord,
    I am at loss. My output is exactly the same as you the attached. I even created the code before you had posted you data based on the text you posted on this page,so I do not know what you wanted.

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,932

    Re: Converting a matrix into multiple row format

    Try this one
    Please Login or Register  to view this content.

+ 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