I have a spreadsheet which contains rows which have part number data in them
(in this case the spreadsheet is created from a Bill of Material report which
has the components' info in the rows).

This sheet has reference designator (locations of components on a circuit
board) column which contains a variable number of locations.

I can do a text to columns parse on the location column to get x number of
columns with each reference designator in a separate colum. How do I/ can I
repeat the part number in column A with a separate individual reference
designator?

I want to end up with something like:

Original: PN XYZ | locations x2,y2,z2

Desired result: PN XYZ | location X2
PN XYZ | location Y2
PN XYZ | location Z2

Excel is a wonderful tool, but I'm still relatively new to more
sophisticated features. Is this doable?

Thanks in advance,

Dave

Hi Dave......, (not revealing your full name is not very friendly)

The following macro will do what you ask, though I'm not sure if that is 1 column or 2 column
in the stub before the arguments will assume it is 1,
if it is 1 then use
stub_columns = 1 'columns A:
arg_columns = 3 'columns B for 3 columns

Sub Split_2_splits()
Dim stub_columns As Long, arg_columns As Long, lastrow As Long
stub_columns = 3 'columns A:B
arg_columns = 3 'columns C for 3 columns
Dim oldSht As Worksheet, newSht As Worksheet
Dim r As Long, c As Long, nr As Long, ac As Long
Dim ac_from As Long, ac_to As Long
ac_from = stub_columns + 1
ac_to = stub_columns + arg_columns
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
nr = 0

Set oldSht = ActiveSheet
Application.DisplayAlerts = False
On Error Resume Next
Sheets("new_work").Delete
On Error GoTo 0
Application.DisplayAlerts = True

ActiveWorkbook.Worksheets.Add(After:=ActiveSheet). Name = "new_work"
Set newSht = ActiveSheet
If lastrow < 11 Then MsgBox lastrow
For r = 1 To lastrow
For ac = ac_from To ac_to
If Trim(oldSht.Cells(r, ac)) < "" Then
nr = nr + 1
For c = 1 To stub_columns
newSht.Cells(nr, c).Formula = oldSht.Cells(r, c).Formula
newSht.Cells(nr, c).NumberFormat = oldSht.Cells(r, c).NumberFormat
newSht.Cells(nr, c).Font.ColorIndex = oldSht.Cells(r, c).Font.ColorIndex
Next c
newSht.Cells(nr, ac_from).Formula = oldSht.Cells(r, ac).Formula
newSht.Cells(nr, ac_from).NumberFormat = oldSht.Cells(r, ac).NumberFormat
newSht.Cells(nr, ac_from).Font.ColorIndex = oldSht.Cells(r, ac).Font.ColorIndex
End If
Next ac
Next r
End Sub


If not familiar with macros then see
http://www.mvps.org/dmcritchie/excel....htm#havemacro

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Dave" wrote in message ...
I have a spreadsheet which contains rows which have part number data in them
(in this case the spreadsheet is created from a Bill of Material report which
has the components' info in the rows).

This sheet has reference designator (locations of components on a circuit
board) column which contains a variable number of locations.

I can do a text to columns parse on the location column to get x number of
columns with each reference designator in a separate colum. How do I/ can I
repeat the part number in column A with a separate individual reference
designator?

I want to end up with something like:

Original: PN XYZ | locations x2,y2,z2

Desired result: PN XYZ | location X2
PN XYZ | location Y2
PN XYZ | location Z2

Excel is a wonderful tool, but I'm still relatively new to more
sophisticated features. Is this doable?

Thanks in advance,

Dave

David,

Woaaa! That's a big macro! Thanks much for your help. I have used macros
before, but not this sophisticated. I'll take some time to digest this.

But thanks again. Your help is much appreciated.

Oh, my name is Dave Schiffer

"David McRitchie" wrote:

Hi Dave......, (not revealing your full name is not very friendly)

The following macro will do what you ask, though I'm not sure if that is 1 column or 2 column
in the stub before the arguments will assume it is 1,
if it is 1 then use
stub_columns = 1 'columns A:
arg_columns = 3 'columns B for 3 columns

Sub Split_2_splits()
Dim stub_columns As Long, arg_columns As Long, lastrow As Long
stub_columns = 3 'columns A:B
arg_columns = 3 'columns C for 3 columns
Dim oldSht As Worksheet, newSht As Worksheet
Dim r As Long, c As Long, nr As Long, ac As Long
Dim ac_from As Long, ac_to As Long
ac_from = stub_columns + 1
ac_to = stub_columns + arg_columns
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
nr = 0

Set oldSht = ActiveSheet
Application.DisplayAlerts = False
On Error Resume Next
Sheets("new_work").Delete
On Error GoTo 0
Application.DisplayAlerts = True

ActiveWorkbook.Worksheets.Add(After:=ActiveSheet). Name = "new_work"
Set newSht = ActiveSheet
If lastrow < 11 Then MsgBox lastrow
For r = 1 To lastrow
For ac = ac_from To ac_to
If Trim(oldSht.Cells(r, ac)) < "" Then
nr = nr + 1
For c = 1 To stub_columns
newSht.Cells(nr, c).Formula = oldSht.Cells(r, c).Formula
newSht.Cells(nr, c).NumberFormat = oldSht.Cells(r, c).NumberFormat
newSht.Cells(nr, c).Font.ColorIndex = oldSht.Cells(r, c).Font.ColorIndex
Next c
newSht.Cells(nr, ac_from).Formula = oldSht.Cells(r, ac).Formula
newSht.Cells(nr, ac_from).NumberFormat = oldSht.Cells(r, ac).NumberFormat
newSht.Cells(nr, ac_from).Font.ColorIndex = oldSht.Cells(r, ac).Font.ColorIndex
End If
Next ac
Next r
End Sub


If not familiar with macros then see
http://www.mvps.org/dmcritchie/excel....htm#havemacro

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Dave" wrote in message ...
I have a spreadsheet which contains rows which have part number data in them
(in this case the spreadsheet is created from a Bill of Material report which
has the components' info in the rows).

This sheet has reference designator (locations of components on a circuit
board) column which contains a variable number of locations.

I can do a text to columns parse on the location column to get x number of
columns with each reference designator in a separate colum. How do I/ can I
repeat the part number in column A with a separate individual reference
designator?

I want to end up with something like:

Original: PN XYZ | locations x2,y2,z2

Desired result: PN XYZ | location X2
PN XYZ | location Y2
PN XYZ | location Z2

Excel is a wonderful tool, but I'm still relatively new to more
sophisticated features. Is this doable?

Thanks in advance,

Dave

Hi Dave,
You're welcome. The macro is a bit bigger now, see
SNAKECOLS, How to snake columns to use fewer pages
http://www.mvps.org/dmcritchie/excel...l.htm3simplify
look for Split_2_splits() and your own customization would be needed.

It will copy an paste the formats used
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

"Dave" wrote
Woaaa! That's a big macro! Thanks much for your help. I have used macros
before, but not this sophisticated. I'll take some time to digest this.

But thanks again. Your help is much appreciated.
Oh, my name is Dave Schiffer