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

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.

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.

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.

Try this one

Please Login or Register  to view this content.