How to Combine Cells using Other Cell
 

I have a worksheet with lots of data and I need to combine some data into
cells using a check of adjacent cells.

Sample:

column
a b
rows1 115 D001
2 115 D005
3 118 F003
4 120 D002
5 120 D003
6 120 F002
7 120 F005
ETC

I need to have the formula check column a, rows 1 through whatever to see if
they are the same, if they are to combine the data in column b row 1 through
whatever into the first row column b.

sample out

columns
a b
rows 115 d001 d005
118 d003
120 d002 doo3 f002 f005

Can anyone figure out if this is possible and how I would go about doing it?

I have tried lots of ways and can't get it to work.

Thanks.

--
PLL

How to Combine Cells using Other Cell
 

Hi,

Right click your sheet tab, view code and past this in and run it

Sub copyit()
Dim myrange, MyRange1 As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For x = 1 To lastrow
For y = 1 + x To lastrow
If Cells(x, 1).Value = Cells(y, 1).Value Then
If MyRange1 Is Nothing Then
Set MyRange1 = Rows(y).EntireRow
Rows(x).End(xlToRight).Offset(, 1).Value = Cells(y, 2).Value
Else
Set MyRange1 = Union(MyRange1, Rows(y).EntireRow)
Rows(x).End(xlToRight).Offset(, 1).Value = Cells(y, 2).Value
End If
End If
Next
Next
MyRange1.Select
Selection.Delete
End Sub

Mike

"pll" wrote:

I have a worksheet with lots of data and I need to combine some data into
cells using a check of adjacent cells.

Sample:

column
a b
rows1 115 D001
2 115 D005
3 118 F003
4 120 D002
5 120 D003
6 120 F002
7 120 F005
ETC

I need to have the formula check column a, rows 1 through whatever to see if
they are the same, if they are to combine the data in column b row 1 through
whatever into the first row column b.

sample out

columns
a b
rows 115 d001 d005
118 d003
120 d002 doo3 f002 f005

Can anyone figure out if this is possible and how I would go about doing it?

I have tried lots of ways and can't get it to work.

Thanks.

--
PLL

How to Combine Cells using Other Cell
 

On reflection this bit should be in an IF statement

If Not MyRange1 Is Nothing Then
MyRange1.Select
Selection.Delete
End If

Mike

"Mike H" wrote:

Hi,

Right click your sheet tab, view code and past this in and run it

Sub copyit()
Dim myrange, MyRange1 As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For x = 1 To lastrow
For y = 1 + x To lastrow
If Cells(x, 1).Value = Cells(y, 1).Value Then
If MyRange1 Is Nothing Then
Set MyRange1 = Rows(y).EntireRow
Rows(x).End(xlToRight).Offset(, 1).Value = Cells(y, 2).Value
Else
Set MyRange1 = Union(MyRange1, Rows(y).EntireRow)
Rows(x).End(xlToRight).Offset(, 1).Value = Cells(y, 2).Value
End If
End If
Next
Next
MyRange1.Select
Selection.Delete
End Sub

Mike

"pll" wrote:

I have a worksheet with lots of data and I need to combine some data into
cells using a check of adjacent cells.

Sample:

column
a b
rows1 115 D001
2 115 D005
3 118 F003
4 120 D002
5 120 D003
6 120 F002
7 120 F005
ETC

I need to have the formula check column a, rows 1 through whatever to see if
they are the same, if they are to combine the data in column b row 1 through
whatever into the first row column b.

sample out

columns
a b
rows 115 d001 d005
118 d003
120 d002 doo3 f002 f005

Can anyone figure out if this is possible and how I would go about doing it?

I have tried lots of ways and can't get it to work.

Thanks.

--
PLL

How to Combine Cells using Other Cell
 

I think you need to add one more variable:
Dim lastrow As Long

Worked fine for me with that in there.

Regards,
Ryan---

--
RyGuy


"Mike H" wrote:

On reflection this bit should be in an IF statement

If Not MyRange1 Is Nothing Then
MyRange1.Select
Selection.Delete
End If

Mike

"Mike H" wrote:

Hi,

Right click your sheet tab, view code and past this in and run it

Sub copyit()
Dim myrange, MyRange1 As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For x = 1 To lastrow
For y = 1 + x To lastrow
If Cells(x, 1).Value = Cells(y, 1).Value Then
If MyRange1 Is Nothing Then
Set MyRange1 = Rows(y).EntireRow
Rows(x).End(xlToRight).Offset(, 1).Value = Cells(y, 2).Value
Else
Set MyRange1 = Union(MyRange1, Rows(y).EntireRow)
Rows(x).End(xlToRight).Offset(, 1).Value = Cells(y, 2).Value
End If
End If
Next
Next
MyRange1.Select
Selection.Delete
End Sub

Mike

"pll" wrote:

I have a worksheet with lots of data and I need to combine some data into
cells using a check of adjacent cells.

Sample:

column
a b
rows1 115 D001
2 115 D005
3 118 F003
4 120 D002
5 120 D003
6 120 F002
7 120 F005
ETC

I need to have the formula check column a, rows 1 through whatever to see if
they are the same, if they are to combine the data in column b row 1 through
whatever into the first row column b.

sample out

columns
a b
rows 115 d001 d005
118 d003
120 d002 doo3 f002 f005

Can anyone figure out if this is possible and how I would go about doing it?

I have tried lots of ways and can't get it to work.

Thanks.

--
PLL