Need VBA Sub for Transposing Data in Columns to Rows

**Phoenix1975** · 04-15-2013, 03:07 PM

I have a data file representing a list of orders along with products associated with those orders. The original data looked something like this:

Order# | Product
CD-23996 | 01. Database 02. Application 03. Middleware
CD-23796 | 01. Internet Developer Suite 02. Programmer 03. Real Application Clusters 04. RAC Enterprise Edition
CD-24117 | 01. Standard Edition 02. Application Server

I inserted commas between the products and used text to columns on the source data to get something like this:

Order# | Product1 | Product2 | Product3 | Product4
CD-23996 | 01. Database | 02. Application | 03. Middleware
CD-23796 | 01. Internet Developer Suite | 02. Programmer | 03. Real Application Clusters | 04. RAC Enterprise Edition
CD-24117 | 01. Standard Edition | 02. Application Server

Now I'm looking for some VBA code I can run that creates rows for each of the products broken out per order # given. So the output should look something like this:

Order# | Product
CD-23996 | 01. Database
CD-23996 | 02. Application
CD-23996 | 03. Middleware
CD-23796 | 01. Internet Developer Suite
CD-23796 | 02. Programmer
CD-23796 | 03. Real Application Clusters
CD-23796 | 04. RAC Enterprise Edition
CD-24117 | 01. Standard Edition
CD-24117 | 02. Application Server

Would really appreciate the help!

**AB33** · 04-15-2013, 03:15 PM

Phoenix,
It is difficult to see your data in text format. Please attach a sample where one sheet should have a raw data and another sheet as your desired result. To attach a sample, Go to advance then attachment.

**Phoenix1975** · 04-15-2013, 04:07 PM

Thanks for replying AB33,

I've attached a spreadsheet with "InputData" and "OutputData". Let me know if there's anything else you need to know.

Regards,

**AB33** · 04-15-2013, 04:21 PM

You can have as many columns as you wish as long as they transposed in to 2 columns. Output in new sheet created by the code.

**Phoenix1975** · 04-15-2013, 05:35 PM

That worked! Great. Thank you!

This thread is SOLVED.

**AB33** · 04-15-2013, 05:41 PM

Phoenix,
You are welcome!

**smiley1404** · 04-17-2013, 04:13 PM

Hello - I'm not an expert and tried to adapt your code to my situation unsuccessfully. What if I need to keep the first 9 columns as they are (instead of just 1) and keep repeating the data there and then transpose all columns after that. Also, as opposed to transposing every column (after the first 9), is it possible to only take every second column?

**AB33** · 04-17-2013, 04:23 PM

Smiley,
I know you just joined. The forum rules states that, you can not post another thread on someone else's thread.
Please post it as new thread. Go to the main page, on the top left hand side, you see post a new thread. Please attach a sample, removing any confidential info. Your sample should show one sheet with raw data and another sheet as your desired result as you can from this thread.

**smiley1404** · 04-17-2013, 04:41 PM

Apologies about that - I have posted a new thread - thanks!

Need VBA Sub for Transposing Data in Columns to Rows

LinkBack

Thread Tools

Rate This Thread

Display

Need VBA Sub for Transposing Data in Columns to Rows

Re: Need VBA Sub for Transposing Data in Columns to Rows

Re: Need VBA Sub for Transposing Data in Columns to Rows

Re: Need VBA Sub for Transposing Data in Columns to Rows

Re: Need VBA Sub for Transposing Data in Columns to Rows

Re: Need VBA Sub for Transposing Data in Columns to Rows

Re: Need VBA Sub for Transposing Data in Columns to Rows

Re: Need VBA Sub for Transposing Data in Columns to Rows

Re: Need VBA Sub for Transposing Data in Columns to Rows

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions