Macro to compare and move data from one worksheet to another

D

dwood

New Member
Joined
Nov 29, 2006
Messages
12
I have two worksheets. Sheet 1 contains two columns of data. Column A contains a list of account numbers and column B contains demographic information about the account number in column A. Sheet 2 contains a list of account numbers in column A and Column B is blank. I need a macro that will look at the account numbers in column A and if it finds the identical account number in column A of Sheet B, copy the demographic data in Column B of Sheet A to Column B of Sheet 2. Since the two worksheets do not necessarily contain the same account numbers, they will not match up row by row. If it cannot find a match in Sheet 2 for an account number in Sheet 1, I need it to skip to the next account number and repeat the process. When the macro reaches the last account number in Sheet 1, I need it to stop. This is a reoccurring monthly report with approximately 200 account numbers and the demographic data is carried forward month to month.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
dwood,


Sample worksheets:


Excel Workbook
AB
1account numberdemographic
212345demographic for 12345
312554demographic for 12554
412555demographic for 12555
5
Sheet1





Excel Workbook
AB
1account numberdemographic
212121
312345
412350
512555
6
Sheet2





After the macro:


Excel Workbook
AB
1account numberdemographic
212121
312345demographic for 12345
412350
512555demographic for 12555
6
Sheet2





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 UpdateW2()
' hiker95, 06/16/2011
' http://www.mrexcel.com/forum/showthread.php?t=557784
Dim w1 As Worksheet, w2 As Worksheet
Dim c As Range, FR As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
Set w2 = Worksheets("Sheet2")
For Each c In w1.Range("A2", w1.Range("A" & Rows.Count).End(xlUp))
  FR = 0
  On Error Resume Next
  FR = Application.Match(c, w2.Columns(1), 0)
  On Error GoTo 0
  If FR <> 0 Then w2.Range("B" & FR).Value = c.Offset(, 1)
Next c
Application.ScreenUpdating = True
End Sub


Then run the UpdateW2 macro.


If the above macro does not work correctly, then we will need screenshots of the two worksheets:

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:
 
Upvote 0
hiker95
On the initial workbook where the macro was comparing one row of data in worksheet 1 to one row of data in worksheet 2, the macro worked correctly. However, after I modified the parameters of the macro and applied it to the worksheets below, I realized it needs to compare columns B and C of worksheet 2 to column B and C of worksheet 1, as these two columns make up a complete loan number. If the data in columns B and C of worksheet 2 match the data in columns B and C of worksheet 1, then the macro needs to copy the data in column F of worksheet 1 to column F of worksheet 2. I have been able to modify the macro and apply it to various workbooks where it is only looking at one column of data. However, I do not know how to make it look at two columns of data simultaneously. I would appreciate your help on this.

Thanks,

dwood
Excel Workbook
ABCDE
7NameAcct #LSBalanceStatus
8STEPHENS4363459615761.43Chap 13
9SMITH6353606573356.4Chap 13
10BISHOP8434850311508.4Chap 13
11BISHOP8434850368652.24Chap 13
12BISHOP8434850393506.22Chap 13
13THOMAS75257832212681.35Chap 13
14DANIELS6626601261083.48Legal
15FOSTER7606744022660.66Chap 13
16MATHEWS9666920618685.71Other
17PAGE3757398554700.8Other
Sheet1
Excel 2003
Excel Workbook
ABCDE
7NameAcct #LSBalanceStatus
8STEPHENS4363459615761.43
9SMITH6353606573356.4
10BISHOP8434850393506.22
11BISHOP8434850311508.4
12BISHOP8434850368652.24
13THOMAS75257832212681.35
14DANIELS6626601261083.48
15FOSTER7606744022660.66
16MATHEWS9666920618685.71
17PAGE3757398554700.8
Sheet2
Excel 2003
 
Upvote 0
hiker95

Correction on the two worksheets. I deleted one of the columns, so the data that needs to be copied is column E, rather than F.

Thanks,

dwood
 
Upvote 0
dwood,


Sample worksheets before the macro:


Excel Workbook
ABCDE
7NameAcct #LSBalanceStatus
8STEPHENS4363459615761.43Chap 13
9SMITH6353606573356.4Chap 13
10BISHOP8434850311508.4Chap 13
11BISHOP8434850368652.24Chap 13
12BISHOP8434850393506.22Chap 13
13THOMAS75257832212681.35Chap 13
14DANIELS6626601261083.48Legal
15FOSTER7606744022660.66Chap 13
16MATHEWS9666920618685.71Other
17PAGE3757398554700.8Other
18
Sheet1





Excel Workbook
ABCDE
7NameAcct #LSBalanceStatus
8STEPHENS4363459615761.43
9SMITH6353606573356.4
10BISHOP8434850393506.22
11BISHOP8434850311508.4
12BISHOP8434850368652.24
13THOMAS75257832212681.35
14DANIELS6626601261083.48
15FOSTER7606744022660.66
16MATHEWS9666920618685.71
17PAGE3757398554700.8
18
Sheet2





After the macro:


Excel Workbook
ABCDE
7NameAcct #LSBalanceStatus
8STEPHENS4363459615761.43Chap 13
9SMITH6353606573356.4Chap 13
10BISHOP8434850393506.22Chap 13
11BISHOP8434850311508.4Chap 13
12BISHOP8434850368652.24Chap 13
13THOMAS75257832212681.35Chap 13
14DANIELS6626601261083.48Legal
15FOSTER7606744022660.66Chap 13
16MATHEWS9666920618685.71Other
17PAGE3757398554700.8Other
18
Sheet2





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 UpdateW2Status()
' hiker95, 06/22/2011
' http://www.mrexcel.com/forum/showthread.php?t=557784
Dim w1 As Worksheet, w2 As Worksheet
Dim c As Range, FR As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
Set w2 = Worksheets("Sheet2")
w1.Range("F8:F" & w1.Cells(Rows.Count, 2).End(xlUp).Row).FormulaR1C1 = "=RC[-4]&RC[-3]"
w2.Range("F8:F" & w2.Cells(Rows.Count, 2).End(xlUp).Row).FormulaR1C1 = "=RC[-4]&RC[-3]"
For Each c In w1.Range("F8", w1.Range("F" & Rows.Count).End(xlUp))
  FR = 0
  On Error Resume Next
  FR = Application.Match(c, w2.Columns(6), 0)
  On Error GoTo 0
  If FR <> 0 Then w2.Range("E" & FR).Value = c.Offset(, -1)
Next c
w1.Range("F8:F" & w1.Cells(Rows.Count, 2).End(xlUp).Row).ClearContents
w2.Range("F8:F" & w2.Cells(Rows.Count, 2).End(xlUp).Row).ClearContents
w2.Activate
Application.ScreenUpdating = True
End Sub


Then run the UpdateW2Status macro.
 
Upvote 0
dwood

I used the first code you provided on this thread, but the data in my column a in both sheet 1 and sheet 2 have multiple iterations of the same "Stock Num" so when I run it, it only copies over the data for the first interation:

[TABLE="width: 145"]
<colgroup><col style="width: 78pt; mso-width-source: userset; mso-width-alt: 3697;" width="104"> <col style="width: 67pt; mso-width-source: userset; mso-width-alt: 3157;" width="89"> <tbody>[TR]
[TD="width: 104, bgcolor: transparent"]STOCK NUM[/TD]
[TD="width: 89, bgcolor: transparent"]CATEGORY[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]001874757[/TD]
[TD="bgcolor: transparent"]WIGIT 14[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]015663094[/TD]
[TD="bgcolor: transparent"]WIGIT 8[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]015663094[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]015663094[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]015663094[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]015663094[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]015663094[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]011354379[/TD]
[TD="bgcolor: transparent"]WIGIT 26[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]015166825[/TD]
[TD="bgcolor: transparent"]WIGIT 26[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]014523527[/TD]
[TD="bgcolor: transparent"]WIGIT 32[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]014523527[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]014526771[/TD]
[TD="bgcolor: transparent"]WIGIT 32[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]014526771[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]014526771[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]011308077[/TD]
[TD="bgcolor: transparent"]WIGIT 26[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]010409653[/TD]
[TD="bgcolor: transparent"]WIGIT 10[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]013291515[/TD]
[TD="bgcolor: transparent"]WIGIT 2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]014672638[/TD]
[TD="bgcolor: transparent"]WIGIT 2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]011651125[/TD]
[TD="bgcolor: transparent"]WIGIT 2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]014386963[/TD]
[TD="bgcolor: transparent"]WIGIT 7[/TD]
[/TR]
</tbody>[/TABLE]

Is there a way to get it to fill them all in on sheet 2 column B?

Thanks

cjc2040
 
Upvote 0
cjc2040,

Welcome to the MrExcel forum.

What version of Excel are you using?


Your data structure is different then the data for dwood. And, you do not want to hijack a post.

Please start your own New Post, with your own Title.


Can you post the raw data worksheet, and, post the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.


If you are not able to give us screenshots:
You can upload your workbook to Box Net,

sensitive data scrubbed/removed/changed

mark the workbook for sharing
and provide us with a link to your workbook.


Then send me a Private Message with a link to your New Post and I will have a look.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
lookup question
Replies
3
Views
133
ajm
A
A
  • Question Question
Excel - looking up data with multiple criteria from table/array, date must be same or nearest before the target cell
Replies
8
Views
510
Peter_SSs
Peter_SSs
L
  • Solved
VBA Match Cells, Copy Adjacent Cells
2
Replies
11
Views
838
Fluff
Fluff
S
  • Question Question
Copy rows from multiple worksheets into multiple worksheets in another workbook based on criteria
Replies
1
Views
301
eiloken
E
M
  • Question Question
VBA Subtotal 47,000+ Rows
Replies
1
Views
202
igold
igold

Forum statistics

Threads
1,223,275
Messages
6,171,119
Members
452,381
Latest member
Nova88

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