Excel 2010 Create New Rows

I have an Excel spreadsheet that I want to create new rows and copy  based on weather or not the column 'CEMLI ID' has multiple values. In other words if for example the CEMLI ID field has three values (E-201, E-195, E-304) then I want to create new rows for each value base on the multi value column CEMLI ID..

Issue ID      L3 Process      CEMLI ID
144      GL_02.10 Execute  E-201, E-195, E-304......Initial row

Issue ID      L3 Process              CEMLI ID
144      GL_02.10 Execute  E-201.... Result
144      GL_02.10 Execute  E-195.... Result
144      GL_02.10 Execute  E-304 ... Result

I have attached a sample spreadsheet
Sample.xlsx
shieldscoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
Try this macro
It fails where the sequence in column 3 is different from the sequence in column 4

Sub insertrowspercemliid()
    Dim sws As Worksheet
    Dim tws As Worksheet
    Dim shname As String
    Dim cel As Range
    Dim itmlst() As String
    Dim strt As String
    Dim fins As String
    Dim i As Long
    Set sws = ActiveSheet
    shname = sws.Name
    Set tws = ThisWorkbook.Worksheets.Add(sws)
    For Each cel In sws.Range("A:A")
        If cel <> "" Then
            itmlst = Split(cel.Offset(, 2), ",")
            If UBound(itmlst) = 0 Then
                cel.Resize(, 18).Copy tws.Range("A" & Rows.Count).End(xlUp).Offset(1)
            Else
                For i = 0 To UBound(itmlst)
                    cel.Resize(, 18).Copy tws.Range("A" & Rows.Count).End(xlUp).Offset(1)
                    tws.Range("A" & Rows.Count).End(xlUp).Offset(, 2) = itmlst(i)
                    strt = InStr(cel.Offset(, 3), itmlst(i))
                    If strt = 0 Then
                        strt = 1
                        fins = Len(cel.Offset(, 3)) - strt + 1
                    Else
                        If i = UBound(itmlst) Then
                            fins = Len(cel.Offset(, 3)) - strt + 1
                        Else
                            fins = InStr(cel.Offset(, 3), itmlst(i + 1)) - strt
                            If fins = -1 Then fins = Len(cel.Offset(, 3)) - strt + 1
                        End If
                    End If
                    tws.Range("A" & Rows.Count).End(xlUp).Offset(, 3) = Mid(cel.Offset(, 3), strt, fins)
                Next i
            End If
        End If
    Next cel
End Sub
0
shieldscoAuthor Commented:
I get a runtime error 5 Invalid Procedure call or argument. on line tws.Range("A" & Rows.Count).End(xlUp).Offset(, 3) = Mid(cel.Offset(, 3), strt, fins). CEMLI ID 242
0
shieldscoAuthor Commented:
What do you mean by It fails where the sequence in column 3 is different from the sequence in column 4
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Saqib Husain, SyedEngineerCommented:
For CEMLI ID 242

The sequence in column 3 is

S-121, S-133, I-111      

and the sequence in column 4 is

I-111:  S-121:  S-133:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shieldscoAuthor Commented:
So if the sequence is different can you ignore
0
Saqib Husain, SyedEngineerCommented:
Add

on error resume next

before that line and

on error goto 0

after that line
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.