• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

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
0
AquaBuoy
Asked:
AquaBuoy
1 Solution
 
StephenJRCommented:
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

0
 
AquaBuoyAuthor Commented:
Wonderful! Thank you very much StephenJR.
It's a simple, elegant, and easily adaptable solution.
0
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.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now