Macro to compare and move data from one worksheet to another

dwood · Jun 16, 2011

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.

hiker95 · Jun 16, 2011

dwood,

Sample worksheets:

Excel Workbook

A

B

1

account number

demographic

2

12345

demographic for 12345

3

12554

demographic for 12554

4

12555

demographic for 12555

5

Sheet1

Excel Workbook

A

B

1

account number

demographic

2

12121

3

12345

4

12350

5

12555

6

Sheet2

After the macro:

Excel Workbook

A

B

1

account number

demographic

2

12121

3

12345

demographic for 12345

4

12350

5

12555

demographic 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:

dwood · Jun 16, 2011

The macro works perfectly, thank you very much.

hiker95 · Jun 16, 2011

dwood,

Thanks for the feedback.

Glad I could help.

Come back anytime.

dwood · Jun 21, 2011

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

A

B

C

D

E

7

Name

Acct #

LS

Balance

Status

8

STEPHENS

43634596

1

5761.43

Chap 13

9

SMITH

63536065

7

3356.4

Chap 13

10

BISHOP

84348503

1

1508.4

Chap 13

11

BISHOP

84348503

6

8652.24

Chap 13

12

BISHOP

84348503

9

3506.22

Chap 13

13

THOMAS

75257832

2

12681.35

Chap 13

14

DANIELS

66266012

6

1083.48

Legal

15

FOSTER

76067440

2

2660.66

Chap 13

16

MATHEWS

96669206

1

8685.71

Other

17

PAGE

37573985

5

4700.8

Other

Sheet1

Excel 2003

Excel Workbook

A

B

C

D

E

7

Name

Acct #

LS

Balance

Status

8

STEPHENS

43634596

1

5761.43

9

SMITH

63536065

7

3356.4

10

BISHOP

84348503

9

3506.22

11

BISHOP

84348503

1

1508.4

12

BISHOP

84348503

6

8652.24

13

THOMAS

75257832

2

12681.35

14

DANIELS

66266012

6

1083.48

15

FOSTER

76067440

2

2660.66

16

MATHEWS

96669206

1

8685.71

17

PAGE

37573985

5

4700.8

Sheet2

Excel 2003

dwood · Jun 21, 2011

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

hiker95 · Jun 22, 2011

dwood,

Sample worksheets before the macro:

Excel Workbook

A

B

C

D

E

7

Name

Acct #

LS

Balance

Status

8

STEPHENS

43634596

1

5761.43

Chap 13

9

SMITH

63536065

7

3356.4

Chap 13

10

BISHOP

84348503

1

1508.4

Chap 13

11

BISHOP

84348503

6

8652.24

Chap 13

12

BISHOP

84348503

9

3506.22

Chap 13

13

THOMAS

75257832

2

12681.35

Chap 13

14

DANIELS

66266012

6

1083.48

Legal

15

FOSTER

76067440

2

2660.66

Chap 13

16

MATHEWS

96669206

1

8685.71

Other

17

PAGE

37573985

5

4700.8

Other

18

Sheet1

Excel Workbook

A

B

C

D

E

7

Name

Acct #

LS

Balance

Status

8

STEPHENS

43634596

1

5761.43

9

SMITH

63536065

7

3356.4

10

BISHOP

84348503

9

3506.22

11

BISHOP

84348503

1

1508.4

12

BISHOP

84348503

6

8652.24

13

THOMAS

75257832

2

12681.35

14

DANIELS

66266012

6

1083.48

15

FOSTER

76067440

2

2660.66

16

MATHEWS

96669206

1

8685.71

17

PAGE

37573985

5

4700.8

18

Sheet2

After the macro:

Excel Workbook

A

B

C

D

E

7

Name

Acct #

LS

Balance

Status

8

STEPHENS

43634596

1

5761.43

Chap 13

9

SMITH

63536065

7

3356.4

Chap 13

10

BISHOP

84348503

9

3506.22

Chap 13

11

BISHOP

84348503

1

1508.4

Chap 13

12

BISHOP

84348503

6

8652.24

Chap 13

13

THOMAS

75257832

2

12681.35

Chap 13

14

DANIELS

66266012

6

1083.48

Legal

15

FOSTER

76067440

2

2660.66

Chap 13

16

MATHEWS

96669206

1

8685.71

Other

17

PAGE

37573985

5

4700.8

Other

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.

dwood · Jun 22, 2011

hiker95

The macro works great. Thanks again.

dwood

cjc2040 · Jul 24, 2012

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:

STOCK NUM	CATEGORY
001874757	WIGIT 14
015663094	WIGIT 8
015663094
015663094
015663094
015663094
015663094
011354379	WIGIT 26
015166825	WIGIT 26
014523527	WIGIT 32
014523527
014526771	WIGIT 32
014526771
014526771
011308077	WIGIT 26
010409653	WIGIT 10
013291515	WIGIT 2
014672638	WIGIT 2
011651125	WIGIT 2
014386963	WIGIT 7

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

Thanks

cjc2040

hiker95 · Jul 24, 2012

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.

Macro to compare and move data from one worksheet to another

dwood

New Member

Excel Facts

hiker95

Well-known Member

dwood

New Member

hiker95

Well-known Member

dwood

New Member

dwood

New Member

hiker95

Well-known Member

dwood

New Member

cjc2040

New Member

hiker95

Well-known Member

Similar threads

Forum statistics

Share this page

Macro to compare and move data from one worksheet to another

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

New Member

Well-known Member

New Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock