Separate items in one cell to multiple rows

mljohn

Board Regular
Joined
Aug 26, 2004
Messages
193
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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:

Code:
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

Board Regular
Joined
Aug 26, 2004
Messages
193
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Can you please provide an example as to how your data is laid out?
 

mljohn

Board Regular
Joined
Aug 26, 2004
Messages
193
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Aug 26, 2004
Messages
193
Office Version
  1. 365
Platform
  1. Windows
Compatible with field is where the Machine models are.
 

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

mljohn

Board Regular
Joined
Aug 26, 2004
Messages
193
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Aug 26, 2004
Messages
193
Office Version
  1. 365
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,461
Messages
5,528,935
Members
409,847
Latest member
Foster034
Top