Try...
Sub ParsePartNums()
Dim vDataIn, vNum, vConfigs, vTemp$(), v
Dim n&, i&, k&, j&, x&, lRows&, lCols&
With ActiveSheet
lRows = .Cells(.Rows.Count, 1).End(xlUp).Row
lCols = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
vDataIn = ActiveSheet.Range(Cells(1, 1), Cells(lRows, lCols))
ReDim vTemp(0)
'Parse the data
For n = LBound(vDataIn) To UBound(vDataIn)
If InStr(1, vDataIn(n, 1), ",") 0 Then
vNum = Split(vDataIn(n, 1), "-"): vConfigs = Split(vNum(2), ",")
'Get current num elements and reset counter
j = UBound(vTemp) + 1: x = 0
ReDim Preserve vTemp(UBound(vTemp) + UBound(vConfigs) + 1)
For k = j To UBound(vTemp)
vTemp(k) = Join(Array(vNum(0), vNum(1), vConfigs(x)), "-")
For i = 2 To UBound(vDataIn, 2)
vTemp(k) = Join(Array(vTemp(k), vDataIn(n, i)))
Next 'i
x = x + 1
Next 'k
Else
ReDim Preserve vTemp(UBound(vTemp) + 1)
vTemp(UBound(vTemp)) = vDataIn(n, 1)
For i = 2 To UBound(vDataIn, 2)
vTemp(UBound(vTemp)) = _
Join(Array(vTemp(UBound(vTemp)), vDataIn(n, i)))
Next 'i
End If
Next 'n
'Bypass limitations of WorksheetFunction.Transpose
ReDim vDataOut(1 To UBound(vTemp), 1 To lCols)
For n = 1 To UBound(vTemp)
v = Split(vTemp(n))
For j = 0 To UBound(v)
vDataOut(n, j + 1) = v(j)
Next 'j
Next 'n
'Dump the data into the worksheet
Range("A1").Resize(UBound(vDataOut), lCols) = vDataOut
End Sub
--
Garry
Free usenet access at
http://www.eternal-september.org
Classic
VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.
vb.general.discussion