# Converting a matrix into multiple row format

1. ## 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!!!

2. ## Re: Converting a matrix into multiple row format

Output in sheet3 (Button in sheet3)

3. ## 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. ## 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)
Regards Mick

5. ## 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. ## 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. ## Re: Converting a matrix into multiple row format

Try this one

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

#### 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