| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| combine 2 cells but keep formating of each individual cell | Excel Worksheet Functions | |||
| How do I combine the contents of multiple cells in one cell? | Excel Worksheet Functions | |||
| Combine multiple cells into one cell range. | Excel Worksheet Functions | |||
| How to combine 2 cells' data into 1 cell? | Charts and Charting in Excel | |||
| How to combine 2 cells' text into 1 cell | Excel Discussion (Misc queries) | |||
Hybrid Mode
