I need to consolidate a list into rows

[ScottBraunDesign]

I have a chartwith part numbers in column A and operation numbers in column B

looks like this

025121 | 010
025121 | 020
025121 | 050
025121 | 090
129017 | 010
129017 | 050

I want it to look like this

025121 | 010 | 020 | 050 | 090
129017 | 010 | 050|

and so on,
problem is there are over 7000 part numbers each with 5 to 50 operation
numbers, I cant possibly do this by hand.... can a function tackle this for
me?

 

[Joel]

The code below moves the data from Sheet1 to Sheet2. Change sheet names as
required. The newo and old row number start on Row 1. You can change thses
row numbers as required. I tested the code and there were no problems.


Sub MakeRows()

NewRow = 1
With Sheets("Sheet1")
RowCount = 1
Do While .Range("A" & RowCount) <> ""
PartNO = .Range("A" & RowCount)
OPNO = .Range("B" & RowCount)
With Sheets("Sheet2")
Set c = .Columns("A").Find(what:=PartNO, LookIn:=xlValues,
lookat:=xlWhole)
If c Is Nothing Then
.Range("A" & NewRow) = PartNO
.Range("B" & NewRow) = OPNO
NewRow = NewRow + 1
Else
LastCol = .Cells(c.Row, Columns.Count).End(xlToLeft).Column
.Cells(c.Row, LastCol + 1) = OPNO
End If
End With
RowCount = RowCount + 1
Loop
End With

End Sub

 

[ScottBraunDesign]

where do i enter this code at?

The code below moves the data from Sheet1 to Sheet2. Change sheet names as
required. The newo and old row number start on Row 1. You can change thses
row numbers as required. I tested the code and there were no problems.


Sub MakeRows()

NewRow = 1
With Sheets("Sheet1")
RowCount = 1
Do While .Range("A" & RowCount) <> ""
PartNO = .Range("A" & RowCount)
OPNO = .Range("B" & RowCount)
With Sheets("Sheet2")
Set c = .Columns("A").Find(what:=PartNO, LookIn:=xlValues,
lookat:=xlWhole)
If c Is Nothing Then
.Range("A" & NewRow) = PartNO
.Range("B" & NewRow) = OPNO
NewRow = NewRow + 1
Else
LastCol = .Cells(c.Row, Columns.Count).End(xlToLeft).Column
.Cells(c.Row, LastCol + 1) = OPNO
End If
End With
RowCount = RowCount + 1
Loop
End With

End Sub

 

[Joel]

Go to VBA editor by tye ALT-F11 from worksheet. From VBA menu Insert -
Module. Copy code from posting From SUB ..... to END SUB and paste in VBA
Editor Window.


Macros can be run three ways

1) From VBA by typing F5
2) From VBA menu Run - Run
3) From worksheet menu Tools - Macro - Macros and then choosing MakeRows.


Note: You security setting in Excel should be set to medium which will ask
you if you want macros to run when you open the workbook. This is the safest
way of operating.

From worksheet menu Tools - Macro - Security. Then select Medium.

 

[ScottBraunDesign]

where do i enter this code at?

i tried to put it in a macro module and run it.... it stops here

Set c = .Columns("A").Find(what:=PartNO, LookIn:=xlValues,
lookat:=xlWhole)

 

[ScottBraunDesign]

where do i enter this code at?

i tried to put it in a macro module and run it.... it stops here

Set c = .Columns("A").Find(what:=PartNO, LookIn:=xlValues,
lookat:=xlWhole)

the first part number is in A1 and the first op number is in B1 sheets are
sheet1 (with info) and sheet2 (blank) i have the A1 cell selected when i
start the macro

 

[ScottBraunDesign]

also some of the part numbers have letters, like 1355M98P09

 

[Joel]

Leters and number don't matter.

Long lines when posted on the web wrap and cause errors.

Set c = .Columns("A").Find(what:=PartNO, LookIn:=xlValues, _
lookat:=xlWhole)


The _ is a line continuation character that allows lines to be in two rows.
Make sure theere are no blank rows between the two lines.