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
359 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 45

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

707 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

15 Experts available now in Live!

Get 1:1 Help Now