split multiple semicolon separated values into new rows for multiple columns

**bruno08102013** · 01-02-2014, 06:17 PM

Hello,

I currently have data that reads like the attached. I've gotten quite far cobbling some code together but for the life of me I just can't figure out this last moderation. Can somebody help adjust my code so that the semicolon separated values are split in columns B & C simultaneously and create corresponding rows below with the original rows data in the remaining columns (A, D, E, F, G) ?

The workbook attached contains three tabs. Raw is the raw datam Current Output is the result of my current macro which is also included in the workbook, and Desired Output is what I want it to look like after the macro. Any help would be greatly appreciated, thank you!

Code:

Please Login or Register  to view this content.

**FDibbins** · 01-02-2014, 06:20 PM

Hi and welcome to the forum

Please note - Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

I will add them for you - this time

**bruno08102013** · 01-02-2014, 08:57 PM

thanks! apologies first time posting. Again I appreciate any help that anyone can offer on this one because it's really stumped me.

Thanks again in advance

**Marc L** · 01-02-2014, 09:08 PM

Hi,

any mistake in row #2 / Col 3 of the Desired Output ?

**bruno08102013** · 01-02-2014, 09:23 PM

Hi,

Thanks for the response, yes, that cell should simply read "WATER".

**Marc L** · 01-02-2014, 09:27 PM

Split rows into same original worksheet or another worksheet ?

**bruno08102013** · 01-02-2014, 09:28 PM

original worksheet

**Marc L** · 01-02-2014, 10:08 PM

Please Login or Register  to view this content.

**bruno08102013** · 01-02-2014, 10:25 PM

works excellent thank you!!!

**bruno08102013** · 01-03-2014, 11:15 AM

apologies I am getting a subscript out of range error when I run the code. Since I have some cases where column 2 will be blank and column 3 populated and vice versa (in any case at least one of those columns will always be populated) I suppose this might be the cause of the error. Is there a quick line or two that might account for this case?

This is the line where I am getting the error:

Please Login or Register  to view this content.

**Marc L** · 01-03-2014, 11:20 AM

Can't see, I need a workbook occuring this issue …

**Marc L** · 01-03-2014, 11:29 AM

Please Login or Register  to view this content.

**bruno08102013** · 01-03-2014, 12:05 PM

Code runs without any errors now but I am noticing the only thing it seems to be missing is the case where this is a value in column 3 but not in column 2. In this case the values appear to be missing and are not present in the output. Can we make it so that it still outputs those lines as well? Thanks in advance.

**Marc L** · 01-03-2014, 12:35 PM

OK but I need a workwook with original worksheet with all possibles cases and another worksheet
as Desired result … Hope to avoid a gas factory code !

**bruno08102013** · 01-03-2014, 12:50 PM

cobble-2.xlsm attached! Thank you!

**Marc L** · 01-03-2014, 03:38 PM

Please Login or Register  to view this content.

Hope my reputation will increase …

**bruno08102013** · 01-03-2014, 04:24 PM

Thank you again, I know this must be a pain, but now I'm getting a type mismatch error at the very beginning on the below line:

Please Login or Register  to view this content.

**Marc L** · 01-03-2014, 07:00 PM

I see nothing in this line that can occurs this error, no such error within your last workbook !

And this line is the same than in previous code … Maybe restart Excel, the computer …

Without the same worksheet data …

**bruno08102013** · 01-03-2014, 07:09 PM

i restarted excel and now it works, ha! thank you!!

**bsalv** · 01-03-2014, 07:37 PM

Please Login or Register  to view this content.

**jindon** · 01-04-2014, 12:18 AM

Try

Please Login or Register  to view this content.

**bruno08102013** · 01-05-2014, 05:27 PM

Thank you so much for the help, I am able to execute the data as needed! Thank you all again.

split multiple semicolon separated values into new rows for multiple columns

LinkBack

Thread Tools

Rate This Thread

Display

split multiple semicolon separated values into new rows for multiple columns

Re: split multiple semicolon separated values into new rows for multiple columns

Re: split multiple semicolon separated values into new rows for multiple columns

Re: split multiple semicolon separated values into new rows for multiple columns

Re: split multiple semicolon separated values into new rows for multiple columns

Re: split multiple semicolon separated values into new rows for multiple columns

Re: split multiple semicolon separated values into new rows for multiple columns

Try this :

Re: split multiple semicolon separated values into new rows for multiple columns

Re: split multiple semicolon separated values into new rows for multiple columns

Re: split multiple semicolon separated values into new rows for multiple columns

Try this :

Re: split multiple semicolon separated values into new rows for multiple columns

Re: split multiple semicolon separated values into new rows for multiple columns

Re: split multiple semicolon separated values into new rows for multiple columns

Hope you don't forget any case, try this !

Re: split multiple semicolon separated values into new rows for multiple columns

Re: split multiple semicolon separated values into new rows for multiple columns

Re: split multiple semicolon separated values into new rows for multiple columns

Re: split multiple semicolon separated values into new rows for multiple columns

Re: split multiple semicolon separated values into new rows for multiple columns

Re: split multiple semicolon separated values into new rows for multiple columns

Thread Information

Users Browsing this Thread

Similar Threads

Split multiple values in a cell to multiple rows

Import multiple semicolon separated csv files

CSV file with two columns multiple values separated by pipe. Need to Combine

Best way to parse colums and rows w/ multiple values separated by comma?

Column with semicolon-separated values

Bookmarks

Bookmarks

Posting Permissions