Separate items in one cell to multiple rows

mljohn

Board Regular
Joined
Aug 26, 2004
Messages
196
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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

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
196
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
196
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
196
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
196
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
196
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,651
Messages
5,838,577
Members
430,557
Latest member
MK15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top