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
362 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
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 46

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

914 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now