Solved

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

Posted on 2014-11-19
5
403 Views
Last Modified: 2014-12-31
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
Comment
Question by:janthonyn
[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
5 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40453911
I will upload a very simplified example of this worksheet for your review.

No sample file..
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 40454222
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
 

Author Comment

by:janthonyn
ID: 40479679
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
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40525157
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question