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

I need help with an Excel VBA that will parse values in a cell into a sequential display according to the number of results in each cell

I have an Excel worksheet with cells that have 1 or more than 1 result included in a column of results. If more than 1 result exists in any of these cells, each result is separated from the other by a pipe character. I have a way to copy the row in which multiple results exist, "X" amount of times, according the the value in a number of results column. But now the customer wants me to only have one result in each cell. I will upload a very simplified example of this worksheet for your review.
0
janthonyn
Asked:
janthonyn
1 Solution
 
Haris DjulicCommented:
I will upload a very simplified example of this worksheet for your review.

No sample file..
0
 
Patrick MatthewsCommented:
Assuming:
You have headings in Row 1
Data starts in Row 2
You want to repeat the data in Column A
You want to split the data in Column B

then this will get you started:

Sub Recast()
    
    Dim LastR As Long
    Dim SourceArr As Variant
    Dim ValuesArr As Variant
    Dim DestR As Long
    Dim Counter As Long
    Dim ItemCounter As Long
    Dim DestWs As Worksheet
    
    With ThisWorkbook.Worksheets("Sheet1")
        LastR = .Cells(.Rows.Count, 1).End(xlUp).Row
        SourceArr = .Cells(1, 1).Resize(LastR, 2).Value
    End With
    
    Set DestWs = ThisWorkbook.Worksheets.Add
    With DestWs
        .Cells(1, 1) = "ID"
        .Cells(1, 2) = "Value"
        DestR = 1
        For Counter = 2 To LastR
            ValuesArr = Split(SourceArr(Counter, 2), "|")
            For ItemCounter = LBound(ValuesArr) To UBound(ValuesArr)
                DestR = DestR + 1
                .Cells(DestR, 1) = SourceArr(Counter, 1)
                .Cells(DestR, 2) = ValuesArr(ItemCounter)
            Next
        Next
    End With
    
    MsgBox "Done"
    
End Sub

Open in new window

Q-28566001.xlsm
0
 
janthonynAuthor Commented:
I came up with a different solution, but will test this one. It looks like it could be a better solution or part of a better solution.
0
 
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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