Data concantating

[RobM]

Any idea's as to take data that looks like this:

1st column 2nd column
10075 6L
10075 6M
10075 6O

And make it look like this:

1st column 2nd column 3rd column 4th column
10075 6L 6M 6N

 

[Tim Zych]

A quick answer based on the data is to copy the 2nd column and
pastespecial -> Transpose. Since your 1st column has only 1 unique value
that will work without the need of a macro.

 

[Joel]

Sub move_to_columns()

Sh1RowCount = 1
Sh2RowCount = 0
Sh2ColCount = 2
With Sheets("Sheet1")
ID = ""
Do While .Range("A" & Sh1RowCount) <> ""
NewID = .Range("A" & Sh1RowCount)
Data = .Range("B" & Sh1RowCount)
With Sheets("Sheet2")
If NewID = ID Then
.Cells(Sh2RowCount, Sh2ColCount) = Data
Sh2ColCount = Sh2ColCount + 1
Else
ID = NewID
Sh2RowCount = Sh2RowCount + 1
.Range("A" & Sh2RowCount) = ID
.Cells(Sh2RowCount, "B") = Data
Sh2ColCount = 3
End If
End With
Sh1RowCount = Sh1RowCount + 1
Loop
End With

End Sub

 

[ryguy7272]

Sub newlist()
Set w1 = Sheets("Sheet1")
Set w2 = Sheets("Sheet2")
w2.Cells(1, 1).Value = w1.Cells(1, 1).Value
w2.Cells(1, 2).Value = w1.Cells(1, 2).Value
Ide = Cells(1, 1).Value
w1.Activate
n = Cells(Rows.count, 1).End(xlUp).Row
k = 3
kk = 1
For i = 2 To n
If w1.Cells(i, 1).Value = Ide Then
w2.Cells(kk, k).Value = w1.Cells(i, 2).Value
k = k + 1
Else
kk = kk + 1
k = 3
Ide = w1.Cells(i, 1).Value
w2.Cells(kk, 1).Value = Ide
w2.Cells(kk, 2).Value = w1.Cells(i, 2).Value
End If
Next
End Sub

Regards
Ryan---

 

[RobM]

Thanks Joel, now all I need to do is figure out how to put this in the excel
workbook as a Macro?

Rob