How to Combine Cells using Other Cell

P

pll

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.
 
M

Mike H

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
 
M

Mike H

On reflection this bit should be in an IF statement

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

Mike
 
R

ryguy7272

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

Worked fine for me with that in there.

Regards,
Ryan---
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top