Multiple entries in one cell, split to multiple rows?

[t1000bogart]

Greetings!

I have an Excel 2007 *.xlsx file that is an extract from another program. In each row, each of the cells from "A" through "I" are filled with supporting data. In the cell in the "J" column, I have from none to many references to test case numbers. If there is more than one test case number, they are separated in cell "J" by a line feed character (vbLf?).

I need a macro that will skip row 1, and will loop from row 2 through the last row in the file. If the cell in the "J" column is blank or contains only 1 test case number, the macro just proceeds to the next row. If the cell in the "J" column contains more 2 or more test case numbers, the macro will create a new row, duplicate the data in rows "A" through "I" and then keep working until all the entries in the "J" cell are parsed out.

A sample row 4 in the source file would look like this:

Cells A4 through I4: Contain supporting data

Cell J4:
366
381
382
383
384
385

After the macro runs, rows 4 through 9 would look like this:

Cells A4 through I4: Contain supporting data
Cells A5 through I5: Contain supporting data
Cells A6 through I6: Contain supporting data
Cells A7 through I7: Contain supporting data
Cells A8 through I8: Contain supporting data
Cells A9 through I9: Contain supporting data

Cell J4: 366
Cell J5: 381
Cell J6: 382
Cell J7: 383
Cell J8: 384
Cell J9: 385

Can someone provide me with a quick macro to get this done? I would have preferred to attach a sample file, but the forum won't let me post attachments yet.

Thanks!

Paul

 

[hiker95]

t1000bogart,

Welcome to the MrExcel forum.

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (sensitive data scrubbed/removed/changed) (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net, mark the workbook for sharing, and provide us with a link to your workbook.

 

[t1000bogart]

hiker95,

Thank you for your response.

I am using Excel 2007 and will keep the file in *.xlsx format.

Here is a link to my sample file. The "Source" tab represents how the data will be exported to the spreadsheet. The "Desired Result" tab represents how I would like to see the data after the macro runs.

http://www.box.com/s/lubgyg13re1387l313qv

Thanks in advance for your help!

Paul

 

[hiker95]

t1000bogart,


Sample raw data:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Header A	Header B	Header C	Header D	Header E	Header F	Header G	Header H	Header I	Header J
2	First Data	First Data	First Data	First Data	First Data	First Data	First Data	First Data	First Data
3	Second Data	Second Data	Second Data	Second Data	Second Data	Second Data	Second Data	Second Data	Second Data	405
4	Third Data	Third Data	Third Data	Third Data	Third Data	Third Data	Third Data	Third Data	Third Data
5	Fourth Data	Fourth Data	Fourth Data	Fourth Data	Fourth Data	Fourth Data	Fourth Data	Fourth Data	Fourth Data	127232366377
6	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	127366381382383384385
7
Source

After the macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Header A	Header B	Header C	Header D	Header E	Header F	Header G	Header H	Header I	Header J
2	First Data	First Data	First Data	First Data	First Data	First Data	First Data	First Data	First Data
3	Second Data	Second Data	Second Data	Second Data	Second Data	Second Data	Second Data	Second Data	Second Data	405
4	Third Data	Third Data	Third Data	Third Data	Third Data	Third Data	Third Data	Third Data	Third Data
5	Fourth Data	Fourth Data	Fourth Data	Fourth Data	Fourth Data	Fourth Data	Fourth Data	Fourth Data	Fourth Data	127
6	Fourth Data	Fourth Data	Fourth Data	Fourth Data	Fourth Data	Fourth Data	Fourth Data	Fourth Data	Fourth Data	232
7	Fourth Data	Fourth Data	Fourth Data	Fourth Data	Fourth Data	Fourth Data	Fourth Data	Fourth Data	Fourth Data	366
8	Fourth Data	Fourth Data	Fourth Data	Fourth Data	Fourth Data	Fourth Data	Fourth Data	Fourth Data	Fourth Data	377
9	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	127
10	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	366
11	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	381
12	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	382
13	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	383
14	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	384
15	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	Fifth Data	385
16
Source

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub SplitJ()
' hiker95, 12/06/2011
' http://www.mrexcel.com/forum/showthread.php?t=597119
Dim r As Long, Sp
Application.ScreenUpdating = False
For r = Cells(Rows.Count, "J").End(xlUp).Row To 2 Step -1
  If InStr(Range("J" & r), Chr(10)) > 0 Then
    Sp = Split(Range("J" & r), Chr(10))
    Rows(r + 1).Resize(UBound(Sp)).Insert
    Rows(r).Copy Rows(r + 1).Resize(UBound(Sp))
    Range("J" & r).Resize(UBound(Sp) + 1) = Application.Transpose(Sp)
  End If
Next r
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the SplitJ macro.

 

[hiker95]

t1000bogart,

I am using Excel 2007 and will keep the file in *.xlsx format.

I tried to save your test workbook as an xlsx file, and had the macro in my PERSONAL file, but, I found that the code failed on the Split command.

I could not find anything in the VBA Editor Reference library to correct the above problem.


You could save a blank workbook, with the macro, as a Template file.

Then, open the Template file, and drop your raw data into it, and run the macro.

 

[t1000bogart]

hiker95,

Thank you very much for your prompt attention. I pasted your macro into my source file and it ran like a champ!

As you mentioned, I'll just paste the extract data into Sheet 1, run the macro, and then save the fixed data off to another sheet.

Paul

 

[Garyuk]

Hi,

I have data simular to this but I have multiple data in E,F,G and H

Can the above macro be edited to include E,F,G and H?

Look forward to your comments.

 

[hiker95]

Garyuk,

Welcome to the MrExcel forum.

You do not want to hijack another persons post, expecially if your data set is not exactly the same.

I have data simular to this but I have multiple data in E,F,G and H

Can the above macro be edited to include E,F,G and H?

Yes.


Please start your own New Post, with an appropriate title, and:


What version of Excel are you using?

Can you post the raw data worksheet, and, post the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.


If you are not able to give us screenshots:
You can upload your workbook to Box Net,

sensitive data scrubbed/removed/changed

mark the workbook for sharing
and provide us with a link to your workbook.


Then send me a Private Message with a link to your New Post and I will have a look.