Help with Excel

[yoink 321]

Hey all,

I've got a problem in Excel I was hoping someone here might know how to solve.

I have two columns:

|aaaa|1,2,3|
|bbbb|3,8,9|
|gggg|1,6,7|


What I need to do is break the second column up so that each number appears in a new row, like so:
(periods are just fillers for formatting on here)

|aaaa|1|
|.......|2|
|.......|3|
|bbbb|3|
|.......|8|
|.......|9|
|gggg|1|
|.......|6|
|.......|7|


I was looking to wirte a macro to do this automatically but can't figure out how to do it...

Any tips or help would be much appreciated!

Cheers

[Pia 0]

No clue, I've been trying to figure it out, but being Friday and all I'm all worked out!

Good luck

**Those who refuse to live and learn simply exist**

[dmace 0]

Here ya go


Sub Yoink()

Dim a() As String
Dim i As Integer
i = 1

While Range("A" & i).Value <> ""

a = parseIt(i)

For j = 0 To UBound(a)
Range("B" & i + j).Value = a(j)
Range("A" & i + j + 1).Select
If j <> UBound(a) Then Selection.EntireRow.Insert
Next

i = i + j
Wend


End Sub


Public Function parseIt(iRowNumber As Integer) As String()
Dim k As Integer
Dim j As Integer
Dim s As String
Dim jeux() As String

s = Trim(Cells(iRowNumber, 2).Value)

'let's see if there are more than one set if not just return that
j = InStr(s, ",")

If j = 0 Then
ReDim jeux(0) As String
jeux(0) = s
parseIt = jeux
Exit Function
Else
'so there's at least 2
While j <> 0
ReDim Preserve jeux(k)

jeux(k) = Mid(s, 1, j - 1)
s = Right(s, (Len(s) - j))
j = InStr(s, ",")

k = k + 1
Wend

ReDim Preserve jeux(k)
jeux(k) = s
parseIt = jeux
End If

End Function

[Pia 0]

huh!

**Those who refuse to live and learn simply exist**

[popsjumper 2]

Just guessing here...

Copy
Paste Special/Transpose

???

I don't have Excel on this machine or I would verify before replying.

My reality and yours are quite different.
I think we're all Bozos on this bus.
Falcon5232, SCS8170, SCSA353, POPS9398, DS239

[Randy_H 0]

That is VBA. You will need to access Tools Macro Visual basic editor.

Then create a module and cut-n-paste the code.

010010010110010101100001011101000111000001110101011100110111001101111001

[rwieder 0]

Quote

That is VBA. You will need to access Tools Macro Visual basic editor.

Then create a module and cut-n-paste the code.

Are we talking MSOffice Professional? If so which edition? I run MSOffice Professional Edition 2000 currently, if you have that version, or even MSOffice 1997, PM me, i'd be glad to help. I can write the formula send it to you, then you just copy and paste in the appropriate cell.....done!

-Richard-
"You're Holding The Rope And I'm Taking The Fall"