Hi Rick
I tried to develop an alternative that doesn't need any writing into any worksheet. It will be done in memory completely (except writing the results of course).
You can see that I make use of all the special facilities that arrays offer us.
The macro is built on the example in this thread.
Code:
Sub M_snb()
sn = Sheets(1).Cells(1).CurrentRegion
For j = 1 To UBound(sn)
c00 = c00 & "|" & Replace(String(UBound(Split(sn(j, 3), ",")), "|"), "|", j & "|") & j
Next
sq = Application.Index(sn, Application.Transpose(Split(Mid(c00, 2), "|")), Evaluate("transpose(row(1:" & UBound(sn, 2) & "))"))
sp = Split(Join(Application.Transpose(Application.Index(sn, 0, 3)), ","), ",")
For j = 0 To UBound(sp)
sq(j + 1, 3) = sp(j)
Next
Cells(10, 1).Resize(UBound(sq), UBound(sq, 2)) = sq
End Sub
or another method:
Code:
Sub M_snb0()
sn = Split(Join([transpose(A2:A6 & "_" & B2:B6 & "_" & substitute(C2:C6,", ","," & A2:A6 & "_" & B2:B6 & "_"))], ","), ",")
Cells(20, 1).Resize(UBound(sn) + 1) = Application.Transpose(sn)
Cells(20, 1).CurrentRegion.TextToColumns , , , , False, False, False, False, True, "_"
End Sub
Originally Posted by snb
Reply With Quote
Bookmarks