Experts Exchange connects you with the people and services you need so you can get back to work.

Combine the solution for creating duplicate rows of records based on criteria with a string splitting function

I'm looking for a macro to process a table that looks something like the attached Input.png to produce a result in a new worksheet that looks like the attached Output.png.

The solution provided by R_Rajesh for the question titled "Develop excel macro to create duplicate rows of records based on criteria" is an ideal and elegant method for creating the duplicate records based on the value in the #People field.

I'm hoping that there is a way to add a sub-routine that will split the string in the Names field and insert the name of each individual in the string into a duplicate record. Hope I'm making sense.

The records in my dataset have more fields than the examples and the value in the #People could, in theory, be infinitely large and is based on the number of forward slash delimiters in the Names field.

Many thanks
Input.png
Output.png

AquaBuoy

Asked: 2011-03-10

Advertise Here

Microsoft Excel

1 Solution

LVL 24

StephenJRCommented: 2011-03-10

Here is one approach:

Sub x()

Dim vIn(), i As Long, n As Long

With Range("A1").CurrentRegion
    vIn = .Value
    .Offset(1).ClearContents
End With

For i = LBound(vIn, 1) + 1 To UBound(vIn)
    n = UBound(Split(vIn(i, 2), "/")) + 1
    Range("A" & Rows.Count).End(xlUp)(2).Resize(n) = vIn(i, 1)
    Range("B" & Rows.Count).End(xlUp)(2).Resize(n) = Application.Transpose(Split(vIn(i, 2), "/"))
    Range("C" & Rows.Count).End(xlUp)(2).Resize(n) = vIn(i, 3)
    Range("D" & Rows.Count).End(xlUp)(2) = 1
    Range("D" & Rows.Count).End(xlUp).Resize(n).DataSeries , , , 1
Next i

End Sub

Open in new window

AquaBuoyAuthor Commented: 2011-03-11

Wonderful! Thank you very much StephenJR.
It's a simple, elegant, and easily adaptable solution.

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.

Combine the solution for creating duplicate rows of records based on criteria with a string splitting function

Join & Write a Comment Already a member? Login.

Featured Post