Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
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
Medium Priority
455 Views
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
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

LVL 15

Expert Comment

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

No sample file..
0

LVL 93

Accepted Solution

Patrick Matthews earned 2000 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

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
``````
Q-28566001.xlsm
0

Author Comment

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 49

Expert Comment

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

Question has a verified solution.

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

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
###### Suggested Courses
Course of the Month10 days, 7 hours left to enroll