Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.
Course Of The Month
9 days, 17 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Combine the solution for creating duplicate rows of records based on criteria with a string splitting function
Posted on 2011-03-10
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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2 Comments
Active 7 days ago
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
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month9 days, 17 hours left to enroll
623 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.