Excel VBA to get field separated by semi-colon into multiple "rows"

Good evening,
Let me start that I can get the end result using "text to columns" then filtering the data to copy and paste what I need for every separation (long and painful and quite annoying to do with the macro recorder even with "use relative references" turned on. However, there are many people more experienced and knowledgeable out there who may have a quicker way. The Goal is so that I will be able to summarize on the "Problem Field". I have some SAS software experience where I would have possibly made 3 (my max will be more like 20) temporary tables after the text to columns then appended them back together so the field could be combined. I have not used that since 2007 and I prefer the macro so an end user can do it monthly without SAS software. I am still in the early stages of learning and executing VBA. I need to write a macro to format data as follows:

Raw Data:

Invoice # Customer Name Problem Field
1 Joe Smith Radio; DVD; Wire
2 Jane Doe Wheels; Bolts

Resulting Data:

Invoice # Customer Name Problem Field
1 Joe Smith Radio
1 Joe Smith DVD
1 Joe Smith Wire
2 Jane Doe Wheels
2 Jane Doe Bolts

amyg1120,

about this, "Excel VBA to get field separated by semi-colon into multiple "rows" ".

It appears that you would like each row to be separated into a row of 3 "columns" not "rows"

And Jane Doe row would result in 2 "rows" of 3 "columns" each.

Correct>

cplmckenzie

Hi,

without any idea of the data location, just see Split function …

Thanks, I have no problem separating the problem field into 3 columns. My problem is getting the first two columns to repeat with each individual item broken down from the 3rd column.

Once separated, it's easy ! Attach a sample workbook …

Hi there,
Thanks very much, here is the sample workbook with a tab of where I am at and a tab of where I need to be by the end of the macro. The goal is to be able to summarize based on what is told in the delimited field which is more difficult if you delimit to multiple columns. Also, we do not want to lose visibility of which items relate to which invoice #. I want to make the VBA code easy to adjust if we end up exceeding the 15 delimit max that I am planning on. If there is a way to not have a max, I'd love the info.
Thanks,
Amy

I'm trying to keep it all within one workbook: 1) raw data, 2) working data which is what my first tab is and then 3) results. The results will be copied and pasted by the end user into a separate workbook each month for a tracker to see how many DVDs per month, etc. So destination would be new worksheet. Raw Data has the delimited field. Working Data is where I have delimited it already. Just wanted to show that I can delimit no problem just getting the invoice information to repeat in multiple lines with each separation is the problem. I do want to skip having that max but not sure how to get there just yet. It is entirely possible that an invoice could have 16+ items even if not at this time.

If I well copy, raw worksheet has only the 3 first columns, right ?
In this case I 'll start from this sheet (just need column D be empty).

Yes, you are correct

Hi amyg1120,
maybe so

Please Login or Register  to view this content.

Thanks, that works on the sample for sure and I will try it out on my real data!

Same way I start …

Just insert before last End With line :

Please Login or Register  to view this content.

My little contribution !

Hi guys,
I have really given this a shot on my own as I want to learn and understand the code without asking for help. However, I am hitting a wall.... Here is my actual macro and the test file that I choose on the open file prompt I have within. I thought I was smart enough to adjust for my real life example but apparently not today. I'm pregnant so at least I can blame it on the baby taking my brain power. I greatly appreciate any time you spend!
Thanks,
Amy

7-31-2014 4-28-16 PM.jpg

Hi Amy,
Look at this line in your code

Please Login or Register  to view this content.

(should be Rws instead of 1)