Macro to Move Data into Another Column

[Michael151]

Hello all,

Trying to write a macro that will move data from one column into another column based on the headers in row 1. I have two columns (RNotes and GNotes). These columns are not always next to each other, which is why I’d like to use the header in row 1 to identify. The data in GNotes needs to move into RNotes separated by a space.

In the example below, the data in GNotes moves into RNotes and notes1 and Notes2 are separated by a space in the cell in the RNotes column.


Before:

<table border="0" cellpadding="0" cellspacing="0" width="192"><col style="width: 48pt;" width="64" span="3"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" width="64" height="17">RNotes</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">GNotes</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">notes1</td> <td>
</td> <td>Notes2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">notes1</td> <td>
</td> <td>Notes2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">notes1</td> <td>
</td> <td>Notes2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">notes1</td> <td>
</td> <td>Notes2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">notes1</td> <td>
</td> <td>Notes2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">notes1</td> <td>
</td> <td>Notes2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">notes1</td> <td>
</td> <td>Notes2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">notes1</td> <td>
</td> <td>Notes2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">notes1</td> <td>
</td> <td>Notes2</td> </tr> </tbody></table>

After:

<table border="0" cellpadding="0" cellspacing="0" width="192"><col style="width: 48pt;" width="64" span="3"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" width="64" height="17">RNotes</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">GNotes</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">notes1 Notes2</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">notes1 Notes2</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">notes1 Notes2</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">notes1 Notes2</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">notes1 Notes2</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">notes1 Notes2</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">notes1 Notes2</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">notes1 Notes2</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">notes1 Notes2</td> <td>
</td> </tr> </tbody></table>

Help is appreciated - thank you!

<table border="0" cellpadding="0" cellspacing="0" width="128"><col style="width: 48pt;" width="64" span="2"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" width="64" height="17">
</td> <td style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">
</td></tr><tr style="height: 12.75pt;" height="17"><td colspan="2" style="height: 12.75pt;" height="17">
</td></tr><tr style="height: 12.75pt;" height="17"><td colspan="2" style="height: 12.75pt;" height="17">
</td></tr><tr style="height: 12.75pt;" height="17"><td colspan="2" style="height: 12.75pt;" height="17">
</td></tr><tr style="height: 12.75pt;" height="17"><td colspan="2" style="height: 12.75pt;" height="17">
</td></tr><tr style="height: 12.75pt;" height="17"><td colspan="2" style="height: 12.75pt;" height="17">
</td></tr><tr style="height: 12.75pt;" height="17"><td colspan="2" style="height: 12.75pt;" height="17">
</td></tr><tr style="height: 12.75pt;" height="17"><td colspan="2" style="height: 12.75pt;" height="17">
</td></tr><tr style="height: 12.75pt;" height="17"><td colspan="2" style="height: 12.75pt;" height="17">
</td></tr></tbody></table>

 

[hiker95]

Michael151,

Cross posted, and answered here:
Macro to Move Data into Another Column
http://www.excelforum.com/excel-programming/768723-macro-to-move-data-into-another-column.html


It does not matter what columns the following headings are in RNotes RNotes, as long as they are in row 1, then the macro will move the data per your request.


Sample data before the macro:

Excel Workbook
	B	C	D	E	F	G
1	GNotes					RNotes
2	Notes2					notes1
3	Notes2					notes1
4	Notes2					notes1
5	Notes2					notes1
6	Notes2					notes1
7	Notes2					notes1
8	Notes2					notes1
9	Notes2					notes1
10	Notes2					notes1
11
Sheet1

After the macro:

Excel Workbook
	B	C	D	E	F	G
1	GNotes					RNotes
2						notes1 Notes2
3						notes1 Notes2
4						notes1 Notes2
5						notes1 Notes2
6						notes1 Notes2
7						notes1 Notes2
8						notes1 Notes2
9						notes1 Notes2
10						notes1 Notes2
11
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub FindMoveData()
' stanleydgromjr, 03/17/2011
' http://www.excelforum.com/excel-programming/768723-macro-to-move-data-into-another-column.html
Dim LR As Long, a As Long, RN As Long, GN As Long
Application.ScreenUpdating = False
RN = Application.Match("RNotes", Rows(1), 0)
GN = Application.Match("GNotes", Rows(1), 0)
LR = Cells(Rows.Count, RN).End(xlUp).Row
For a = 2 To LR Step 1
  Cells(a, RN).Value = Cells(a, RN).Value & " " & Cells(a, GN).Value
Next a
Range(Cells(2, GN), Cells(LR, GN)).ClearContents
Columns(RN).AutoFit
Application.ScreenUpdating = True
End Sub

Then run the FindMoveData macro.