software

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, quot;Aquot;).End(xlUp).Row
nr = 0

Set oldSht = ActiveSheet
Application.DisplayAlerts = False
On Error Resume Next
Sheets(quot;new_workquot;).Delete
On Error GoTo 0
Application.DisplayAlerts = True

ActiveWorkbook.Worksheets.Add(After:=ActiveSheet). Name = quot;new_workquot;
Set newSht = ActiveSheet
If lastrow lt;gt; 11 Then MsgBox lastrow
For r = 1 To lastrow
For ac = ac_from To ac_to
If Trim(oldSht.Cells(r, ac)) lt;gt; quot;quot; 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 SubIf not familiar with macros then see
www.mvps.org/dmcritchie/excel....htm#havemacro

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

quot;Davequot; gt; wrote in message ...
gt; I have a spreadsheet which contains rows which have part number data in them
gt; (in this case the spreadsheet is created from a Bill of Material report which
gt; has the components' info in the rows).
gt;
gt; This sheet has reference designator (locations of components on a circuit
gt; board) column which contains a variable number of locations.
gt;
gt; I can do a text to columns parse on the location column to get x number of
gt; columns with each reference designator in a separate colum. How do I/ can I
gt; repeat the part number in column A with a separate individual reference
gt; designator?
gt;
gt; I want to end up with something like:
gt;
gt; Original: PN XYZ | locations x2,y2,z2
gt;
gt; Desired result: PN XYZ | location X2
gt; PN XYZ | location Y2
gt; PN XYZ | location Z2
gt;
gt; Excel is a wonderful tool, but I'm still relatively new to more
gt; sophisticated features. Is this doable?
gt;
gt; Thanks in advance,
gt;
gt; 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

quot;David McRitchiequot; wrote:

gt; Hi Dave......, (not revealing your full name is not very friendly)
gt;
gt; The following macro will do what you ask, though I'm not sure if that is 1 column or 2 column
gt; in the stub before the arguments will assume it is 1,
gt; if it is 1 then use
gt; stub_columns = 1 'columns A:
gt; arg_columns = 3 'columns B for 3 columns
gt;
gt; Sub Split_2_splits()
gt; Dim stub_columns As Long, arg_columns As Long, lastrow As Long
gt; stub_columns = 3 'columns A:B
gt; arg_columns = 3 'columns C for 3 columns
gt; Dim oldSht As Worksheet, newSht As Worksheet
gt; Dim r As Long, c As Long, nr As Long, ac As Long
gt; Dim ac_from As Long, ac_to As Long
gt; ac_from = stub_columns 1
gt; ac_to = stub_columns arg_columns
gt; lastrow = Cells(Cells.Rows.Count, quot;Aquot;).End(xlUp).Row
gt; nr = 0
gt;
gt; Set oldSht = ActiveSheet
gt; Application.DisplayAlerts = False
gt; On Error Resume Next
gt; Sheets(quot;new_workquot;).Delete
gt; On Error GoTo 0
gt; Application.DisplayAlerts = True
gt;
gt; ActiveWorkbook.Worksheets.Add(After:=ActiveSheet). Name = quot;new_workquot;
gt; Set newSht = ActiveSheet
gt; If lastrow lt;gt; 11 Then MsgBox lastrow
gt; For r = 1 To lastrow
gt; For ac = ac_from To ac_to
gt; If Trim(oldSht.Cells(r, ac)) lt;gt; quot;quot; Then
gt; nr = nr 1
gt; For c = 1 To stub_columns
gt; newSht.Cells(nr, c).Formula = oldSht.Cells(r, c).Formula
gt; newSht.Cells(nr, c).NumberFormat = oldSht.Cells(r, c).NumberFormat
gt; newSht.Cells(nr, c).Font.ColorIndex = oldSht.Cells(r, c).Font.ColorIndex
gt; Next c
gt; newSht.Cells(nr, ac_from).Formula = oldSht.Cells(r, ac).Formula
gt; newSht.Cells(nr, ac_from).NumberFormat = oldSht.Cells(r, ac).NumberFormat
gt; newSht.Cells(nr, ac_from).Font.ColorIndex = oldSht.Cells(r, ac).Font.ColorIndex
gt; End If
gt; Next ac
gt; Next r
gt; End Sub
gt;
gt;
gt; If not familiar with macros then see
gt; www.mvps.org/dmcritchie/excel....htm#havemacro
gt;
gt; ---
gt; HTH,
gt; David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
gt; My Excel Pages: www.mvps.org/dmcritchie/excel/excel.htm
gt; Search Page: www.mvps.org/dmcritchie/excel/search.htm
gt;
gt; quot;Davequot; gt; wrote in message ...
gt; gt; I have a spreadsheet which contains rows which have part number data in them
gt; gt; (in this case the spreadsheet is created from a Bill of Material report which
gt; gt; has the components' info in the rows).
gt; gt;
gt; gt; This sheet has reference designator (locations of components on a circuit
gt; gt; board) column which contains a variable number of locations.
gt; gt;
gt; gt; I can do a text to columns parse on the location column to get x number of
gt; gt; columns with each reference designator in a separate colum. How do I/ can I
gt; gt; repeat the part number in column A with a separate individual reference
gt; gt; designator?
gt; gt;
gt; gt; I want to end up with something like:
gt; gt;
gt; gt; Original: PN XYZ | locations x2,y2,z2
gt; gt;
gt; gt; Desired result: PN XYZ | location X2
gt; gt; PN XYZ | location Y2
gt; gt; PN XYZ | location Z2
gt; gt;
gt; gt; Excel is a wonderful tool, but I'm still relatively new to more
gt; gt; sophisticated features. Is this doable?
gt; gt;
gt; gt; Thanks in advance,
gt; gt;
gt; gt; Dave
gt;
gt;
gt;

Hi Dave,
You're welcome. The macro is a bit bigger now, see
SNAKECOLS, How to snake columns to use fewer pages
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: www.mvps.org/dmcritchie/excel/excel.htm

quot;Davequot; gt; wrote
gt; Woaaa! That's a big macro! Thanks much for your help. I have used macros
gt; before, but not this sophisticated. I'll take some time to digest this.
gt;
gt; But thanks again. Your help is much appreciated.
gt; Oh, my name is Dave Schiffer