[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • 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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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