Comma Delim to Rows - Everything Else Copied

**JMcDon1007** · 05-10-2011, 02:53 PM

Hey Guys/Girls,

I'm a first time poster, so please bear with me if I am not 100% spot on with my question.

I have a spreadsheet of data that contains multiple item numbers (one per row) and one column in that list has comma-delim data (2 characters).

It's currently taking 3 employees 2 whole days to sift through this report in order to extract what they need. If we are able to take this data (attached) and blow it out so that we create separate rows for each of the Product Code, it would save a ton of work.

To summarize, I need a way to take all of the comma-delim 2-digit codes in Column O and make them go to one in each row, but also copy all of the data from the row it's in to the single row. In the current example, Row 2 would not change, then Row 3 would be Item 1234 and Product CodeAB along with everything else in the current row copied to that row. Then, Row 4 would be Item 1234 and Product Code AC with everything else in the current row copied to that row.

Finally, the actual data set is 2,455 rows long before I would run the macro so the result could potentially be 400-500,000 rows of data. Also, I would need all columns to be copied over since all columns are used at some point in the data.

P.S. I would like the macro to create the result in a new tab if at all possible....but it's not 100% required.

Thanks so much to whoever can provide assistance.

Take care.

**mrice** · 05-10-2011, 03:32 PM

Try this.

Please Login or Register  to view this content.

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007.

**JMcDon1007** · 05-11-2011, 03:41 PM

Originally Posted by mrice

Try this.

Please Login or Register  to view this content.

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007.

Thank you so much for the help! I just now had a chance to get online and see if I got a reply so I will check out the information and let you know what happens.
Take care,

-John

**JMcDon1007** · 05-12-2011, 04:07 PM

This worked! Thank you again for your help. I had to tweak a few things due to differences in subsequent sheets but overall it got me on the track I needed to be on. I appreciate all the help.

Take care.

**JMcDon1007** · 05-13-2011, 07:49 AM

I just uncovered an issue. I was stress testing this code with a high volume spreadsheet which would have produced well over 400,000 records. The macro ran fine; however, the spreadsheet is saved as a .xls file; therefore, it hits a wall at ~65,000 records. I know that the new Excel 2007 spreadsheets will accommodate a much greater volume of records.

My questions are: If I simply save the original spreadsheet as a .xlsx file, then run the macro, will it produce the larger volume I'm looking for (past ~65,000 rows)? And if so, would there be any necessary changes in the code to facilitate the .xlsx file?

Thanks again for all your help!

**jaslake** · 05-13-2011, 12:56 PM

Hi JMcDon1007

In Excel 2007, try saving the file as an xlsm (macro enabled) file. See if that helps. In reading the code as posted, it doesn't appear to require modification.

**JMcDon1007** · 05-19-2011, 07:57 AM

Thank you Sir! I also discovered that by simply saving the file as an Excel 2007 file would solve the issue. Per your instruction, I will mark this as closed.

Thanks again to everyone for all of their help.