I have a table like this:

Name ID Type
A 12; 14 New
B 13; 15 Old

I want to convert this to:

Name ID Type
A 12 New
A 14 New
B 13 Old
B 15 Old

Dim cell As Range
Dim rng As Range
Dim v As Variant, i As Integer
Dim wksNew As Worksheet
Set rng = Range("B2:B4")
Set wksNew = Workbooks.Add(1).Worksheets(1)
For Each cell In rng.Cells
v = Split(cell.Value, ";")
For i = LBound(v) To UBound(v)
With wksNew.Cells(65000, "A").End(xlUp).Offset(1)
.Cells(1, 1).Value = cell.Offset(, -1).Value
.Cells(1, 2).Value = Trim(v(i))
.Cells(1, 3).Value = cell.Offset(, 1).Value
End With
Next
Next

Adjust the rng setting to be the proper semicolon-delimited list.

--
Tim Zych
SF, CA


"Yoss-22" <Yoss-(E-Mail Removed)> wrote in message
news:2CE6F597-7EFF-45F5-9FE8-(E-Mail Removed)...
>I have a table like this:
>
> Name ID Type
> A 12; 14 New
> B 13; 15 Old
>
> I want to convert this to:
>
> Name ID Type
> A 12 New
> A 14 New
> B 13 Old
> B 15 Old
>
>

Thanks, that helps.

"Tim Zych" wrote:

> Dim cell As Range
> Dim rng As Range
> Dim v As Variant, i As Integer
> Dim wksNew As Worksheet
> Set rng = Range("B2:B4")
> Set wksNew = Workbooks.Add(1).Worksheets(1)
> For Each cell In rng.Cells
> v = Split(cell.Value, ";")
> For i = LBound(v) To UBound(v)
> With wksNew.Cells(65000, "A").End(xlUp).Offset(1)
> .Cells(1, 1).Value = cell.Offset(, -1).Value
> .Cells(1, 2).Value = Trim(v(i))
> .Cells(1, 3).Value = cell.Offset(, 1).Value
> End With
> Next
> Next
>
> Adjust the rng setting to be the proper semicolon-delimited list.
>
> --
> Tim Zych
> SF, CA
>
>
> "Yoss-22" <Yoss-(E-Mail Removed)> wrote in message
> news:2CE6F597-7EFF-45F5-9FE8-(E-Mail Removed)...
> >I have a table like this:
> >
> > Name ID Type
> > A 12; 14 New
> > B 13; 15 Old
> >
> > I want to convert this to:
> >
> > Name ID Type
> > A 12 New
> > A 14 New
> > B 13 Old
> > B 15 Old
> >
> >
>
>
>