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.
janthonynAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Haris DulicCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.