Columns to rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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/getstarted.htm#havemacro
 
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
 
Back
Top