Separate items in one cell to multiple rows

[mljohn]

I have a list of machine models in one cell on one row.
I want to separate the machine models that are listed together in one cell separated by a comma and put each machine model in it's own row.

How do I do this in Excel or in Visual Basic?

Thanks for your amazing help.

You guys are great.

Matt

 

[MrKowz]

An easy way to do this in Excel would be to use Text-To-Columns, use comma as the delimiter. Then copy>paste special(transpose) the result.

For VBA:

Public Sub mljohn()
Dim ModelArray  As Variant, _
    i           As Long
ModelArray = Split(Range("A1").Value, ",")
Application.ScreenUpdating = False
For i = LBound(ModelArray) To UBound(ModelArray)
    Range("A" & i + 1).Value = ModelArray(i)
Next i
Application.ScreenUpdating = True
End Sub

 

[mljohn]

Wonderful.

Another question.

Each machine model has other column fields that I want to duplicate on each row of the transposed machine models.

How do I do that?

Matt

 

[MrKowz]

Can you please provide an example as to how your data is laid out?

 

[mljohn]

I wish I could copy and paste but I don't know how to do that.

Vendor | Category| Printer | Compatible with | OemPartNo |MSEPartNo | PremiumPartNo | PageYield | Price

Here are the fields. Did you want something else?

 

[mljohn]

Compatible with field is where the Machine models are.

 

[MrKowz]

So is this a separate table that has each of the machine models in it, and you are wanting to lookup certain values?

 

[mljohn]

I have a row that has this in the compatible with field:
DCP-1200, 1400, Fax-4750, 5750, 8350p, 8750p, HL 1030, 1230, 1240, 1250, 1270n, 1435, 1440, 1470n

If I put "DCP-1200" in it's own row, I want all the info that was in all the other fields to be duplicated for each machine that was in that one cell before they were separated.

As a partial example it would look like this but would include the rest of the fields.

MSE TONER BROTHER DCP-1200 DR400
MSE TONER BROTHER DCP-1400 DR400
MSE TONER BROTHER Fax-4750 DR400
MSE TONER BROTHER HL 1030 DR400

 

[mljohn]

Can you help me with this?

 

[mljohn]

How do I automate this and also get the other fields to duplicate when the machines in the "compatible with" column are moved to separate rows?