Macro to Move Data into Another Column

M

Michael151

Board Regular
Joined
Sep 20, 2010
Messages
247
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>
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
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
BCDEFG
1GNotesRNotes
2Notes2notes1
3Notes2notes1
4Notes2notes1
5Notes2notes1
6Notes2notes1
7Notes2notes1
8Notes2notes1
9Notes2notes1
10Notes2notes1
11
Sheet1





After the macro:


Excel Workbook
BCDEFG
1GNotesRNotes
2notes1 Notes2
3notes1 Notes2
4notes1 Notes2
5notes1 Notes2
6notes1 Notes2
7notes1 Notes2
8notes1 Notes2
9notes1 Notes2
10notes1 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.


Code: 
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
181
dilshod_k
D
J
  • Question
Moving duplicate Column Data to individual rows in VBA
Replies
4
Views
227
Peter_SSs
Peter_SSs
A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
J
  • Solved
Reducing balance and moving to next column
Replies
3
Views
198
Peter_SSs
Peter_SSs
J
  • Question
Complex Move data from two spreadsheets with similar data in different columns to a specific column in an existing spreadsheet(Sheet 3)
Replies
0
Views
165
JMC57
J

Forum statistics

Threads
1,216,126
Messages
6,129,021
Members
449,480
Latest member
yesitisasport

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top