Compare two sheets and copy match value to new sheet

sharan250 · Aug 12, 2014

Hi,

I am new to macros and am trying to solve a problem where I have some data in sheet1 and sheet2.
Data on Sheet1 is as follows:
#Unit test Maths History Science
1 70 55 95
2 60 64 52
3 50 85 33
4 75 60 62
5 65 70 94
6 40 55 86
7 70 58 41
8 40 60 99

Data on Sheet2 is as follows:
ID #Unit test
1231 1 Maths History Science
1232 3 History Science
1233 5 Science Maths
1234 5 History Maths
1235 2 Science
1236 7 History
1237 8 Maths Science

I want to compare data of sheet2 with sheet1 such that #Unit test for each ID is identified and is looked up in sheet1. Further, subjects of respective ID is identified from Sheet2 is identified and looked up in sheet1. Subjects value from Sheet1 is copied to sheet3 along with ID and #Unit test. Sheet3 should look like:
ID #Unit test Maths History Science
1231 1 70 55 95
1232 3 85 33
1233 5 65 94
1234 5 65 70
1235 2 52
1236 7 58
1237 8 40 99

Any help on this will be highly appreciated.

Thanks.

hiker95 · Aug 12, 2014

sharan250,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?

Are the following two sample raw data worksheets correct?

Excel 2007

A

B

C

D

1

#Unit test

Maths

History

Science

2

1

70

55

95

3

2

60

64

52

4

3

50

85

33

5

4

75

60

62

6

5

65

70

94

7

6

40

55

86

8

7

70

58

41

9

8

40

60

99

10

Sheet1

Excel 2007

A

B

C

D

E

1

ID

#Unit test

2

1231

1

Maths

History

Science

3

1232

3

History

Science

4

1233

5

Science

Maths

5

1234

5

History

Maths

6

1235

2

Science

7

1236

7

History

8

1237

8

Maths

Science

9

Sheet2

Here is a screenshot of worksheet Sheet3 (the BLUE cells are your text results, and, the YELLOW cells are my manual results):

Before I do any coding, which display is correct: the BLUE, or, the YELLOW?

Excel 2007

A

B

C

D

E

F

G

H

I

J

K

1

ID

#Unit test

Maths

History

Science

ID

#Unit test

Maths

History

Science

2

1231

1

70

55

95

1231

1

70

55

95

3

1232

3

85

33

1232

3

85

33

4

1233

5

65

94

1233

5

65

94

5

1234

5

65

70

1234

5

65

70

6

1235

2

52

1235

2

52

7

1236

7

58

1236

7

58

8

1237

8

40

99

1237

8

40

99

9

Sheet3

sharan250 · Aug 12, 2014

Hi Hiker95,

Thanks for your quick response.

I am using excel 2010 and working on PC (windows 7).

Further, the result shown in YELLOW is the right one.

Cheers,

hiker95 said:
sharan250,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?

Are the following two sample raw data worksheets correct?

Excel 2007

A B C D
1 #Unit test Maths History Science
2 1 70 55 95
3 2 60 64 52
4 3 50 85 33
5 4 75 60 62
6 5 65 70 94
7 6 40 55 86
8 7 70 58 41
9 8 40 60 99
10

<tbody>
</tbody>
Sheet1

Excel 2007

A B C D E
1 ID #Unit test
2 1231 1 Maths History Science
3 1232 3 History Science
4 1233 5 Science Maths
5 1234 5 History Maths
6 1235 2 Science
7 1236 7 History
8 1237 8 Maths Science
9

<tbody>
</tbody>
Sheet2

Here is a screenshot of worksheet Sheet3 (the BLUE cells are your text results, and, the YELLOW cells are my manual results):

Before I do any coding, which display is correct: the BLUE, or, the YELLOW?

Excel 2007

A B C D E F G H I J K
1 ID #Unit test Maths History Science ID #Unit test Maths History Science
2 1231 1 70 55 95 1231 1 70 55 95
3 1232 3 85 33 1232 3 85 33
4 1233 5 65 94 1233 5 65 94
5 1234 5 65 70 1234 5 65 70
6 1235 2 52 1235 2 52
7 1236 7 58 1236 7 58
8 1237 8 40 99 1237 8 40 99
9

<tbody>
</tbody>
Sheet3

hiker95 · Aug 12, 2014

sharan250,

the result shown in YELLOW is the right one.

Thank you.

The following is based on you text displays, and, my screenshots. If there will be more than 3 subjects, I will have to adjust the macro.

Sample raw data worksheets:

Excel 2007

A

B

C

D

1

#Unit test

Maths

History

Science

2

1

70

55

95

3

2

60

64

52

4

3

50

85

33

5

4

75

60

62

6

5

65

70

94

7

6

40

55

86

8

7

70

58

41

9

8

40

60

99

10

Sheet1

Excel 2007

A

B

C

D

E

1

ID

#Unit test

2

1231

1

Maths

History

Science

3

1232

3

History

Science

4

1233

5

Science

Maths

5

1234

5

History

Maths

6

1235

2

Science

7

1236

7

History

8

1237

8

Maths

Science

9

Sheet2

After the macro in worksheet Sheet3 (Sheet3 will be cleared before writing to it):

Excel 2007

A

B

C

D

E

1

ID

#Unit test

Maths

History

Science

2

1231

1

70

55

95

3

1232

3

85

33

4

1233

5

65

94

5

1234

5

65

70

6

1235

2

52

7

1236

7

58

8

1237

8

40

99

9

Sheet3

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
2. Open your NEW 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
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:

Sub Compare_Sheet2_to_Sheet1()
' hiker95, 08/12/2014, ME798457
Dim w1 As Worksheet, w2 As Worksheet, w3 As Worksheet
Dim lr As Long
Dim d As Range, c As Range, u As Range, s As Range
Application.ScreenUpdating = False
Set w1 = Sheets("Sheet1")
Set w2 = Sheets("Sheet2")
Set w3 = Sheets("Sheet3")
lr = w2.Cells(Rows.Count, 1).End(xlUp).Row
With w3
  .UsedRange.ClearContents
  .Cells(1, 1).Resize(lr, 2).Value = w2.Range("A1:B" & lr).Value
  .Cells(1, 3).Resize(, 3).Value = Array("Maths", "History", "Science")
  .Columns(1).Resize(, 5).AutoFit
End With
With w2
  For Each d In .Range("B2", .Range("B" & Rows.Count).End(xlUp))
    Set u = w1.Columns(1).Find(d.Value, LookAt:=xlWhole)
    If Not u Is Nothing Then
      For Each c In .Range("C" & d.Row & ":E" & d.Row)
        If c <> "" Then
          Set s = w1.Rows(1).Find(c.Value, LookAt:=xlWhole)
          If Not s Is Nothing Then
            w3.Cells(d.Row, s.Column + 1).Value = w1.Cells(u.Row, s.Column).Value
            Set s = Nothing
          End If
        End If
      Next c
    End If
    Set u = Nothing
  Next d
End With
w3.Activate
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the Compare_Sheet2_to_Sheet1 macro.

DocAElstein · Aug 12, 2014

Hi Sharon,
. Another more basic varianten, that is to say written by a beginner! :

Sub Sharon1() Dim UsedRange1 As Range 'Give your usedrange for table1 a name and allow it to have all properties and methods of a range object Set UsedRange1 = Worksheets("Sheet1").Range("A1").CurrentRegion ' Currentrange property returns the table area. Dim UsedRange1Row As Byte, UsedRange2Row As Byte ' limit tables to 255 rows Let UsedRange1Row = UsedRange1.Rows.Count 'This gives you how many rows in table1 that you have Dim UsedRange2 As Range Set UsedRange2 = Worksheets("Sheet2").Range("A1").CurrentRegion Let UsedRange2Row = UsedRange2.Rows.Count For y = 2 To UsedRange2Row Step 1 'For every row in table 2... Worksheets("Sheet3").Cells(y, 1).Value = Worksheets("Sheet2").Cells(y, 1).Value ' ...Put ID in column 1, .. Worksheets("Sheet3").Cells(y, 2).Value = Worksheets("Sheet2").Cells(y, 2).Value ' ... Put #Unit test in column 2 Next y For j = 2 To UsedRange2Row 'for each row in sheet 2... For i = 3 To 5 '...go through column 3,4 and 5.... For y = 2 To UsedRange1Row '... and go through every row in Sheet 1... Select Case Worksheets("Sheet2").Cells(j, i).Value ' ..then look for Maths, History, or Science Case "Maths" If Worksheets("Sheet2").Cells(j, 2).Value = Worksheets("Sheet1").Cells(y, 1).Value Then ' If maths then Worksheets("Sheet3").Cells(j, 3).Value = Worksheets("Sheet1").Cells(y, 2).Value ' put appropriate value in column 3, sheet5...etc Else End If Case "History" If Worksheets("Sheet2").Cells(j, 2).Value = Worksheets("Sheet1").Cells(y, 1).Value Then Worksheets("Sheet3").Cells(j, 4).Value = Worksheets("Sheet1").Cells(y, 3).Value Else End If Case "Science" If Worksheets("Sheet2").Cells(j, 2).Value = Worksheets("Sheet1").Cells(y, 1).Value Then Worksheets("Sheet3").Cells(j, 5).Value = Worksheets("Sheet1").Cells(y, 4).Value Else End If Case Else End Select Next y Next i Next j End Sub 'Sharon1()

Sheet 1

Book1

A

B

C

D

1

#Unit test

Maths

History

Science

2

1

70

55

95

3

2

60

64

52

4

3

50

85

33

5

4

75

60

62

6

5

65

70

94

7

6

40

55

86

8

7

70

58

41

9

8

40

60

99

10

11

Sheet1

Sheet 2:

Book1

A

B

C

D

E

1

ID

#Unit test

2

1231

1

Maths

History

Science

3

1232

3

History

Science

4

1233

5

Science

Maths

5

1234

5

History

Maths

6

1235

2

Science

7

1236

7

History

8

1237

8

Maths

Science

9

10

Sheet2

Sheet 3 before running Macro:

Book1

A

B

C

D

E

F

1

ID

#Unit test

Maths

History

Science

2

3

4

5

6

Sheet3

Sheet 3 after running macro:

Book1

A

B

C

D

E

F

1

ID

#Unit test

Maths

History

Science

2

1231

1

70

55

95

3

1232

3

85

33

4

1233

5

65

94

5

1234

5

65

70

6

1235

2

52

7

1236

7

58

8

1237

8

40

99

9

Sheet3

File here:

FileSnack | Easy file sharing

Macro in module named Sharon

Good luck
Alan

DocAElstein · Aug 12, 2014

Code again without Typing error "Column 3, sheet 3...etc" not column 3, sheet 5...etc"

Sub Sharon1() Dim UsedRange1 As Range 'Give your usedrange for table1 a name and allow it to have all properties and methods of a range object Set UsedRange1 = Worksheets("Sheet1").Range("A1").CurrentRegion ' Currentrange property returns the table area. Dim UsedRange1Row As Byte, UsedRange2Row As Byte ' limit tables to 255 rows Let UsedRange1Row = UsedRange1.Rows.Count 'This gives you how many rows in table1 that you have Dim UsedRange2 As Range Set UsedRange2 = Worksheets("Sheet2").Range("A1").CurrentRegion Let UsedRange2Row = UsedRange2.Rows.Count For y = 2 To UsedRange2Row Step 1 'For every row in table 2... Worksheets("Sheet3").Cells(y, 1).Value = Worksheets("Sheet2").Cells(y, 1).Value ' ...Put ID in column 1, .. Worksheets("Sheet3").Cells(y, 2).Value = Worksheets("Sheet2").Cells(y, 2).Value ' ... Put #Unit test in column 2 Next y For j = 2 To UsedRange2Row 'for each row in sheet 2... For i = 3 To 5 '...go through column 3,4 and 5.... For y = 2 To UsedRange1Row '... and go through every row in Sheet 1... Select Case Worksheets("Sheet2").Cells(j, i).Value ' ..then look for Maths, History, or Science Case "Maths" If Worksheets("Sheet2").Cells(j, 2).Value = Worksheets("Sheet1").Cells(y, 1).Value Then ' If maths then Worksheets("Sheet3").Cells(j, 3).Value = Worksheets("Sheet1").Cells(y, 2).Value ' put appropriate value in column 3, sheet3...etc Else End If Case "History" If Worksheets("Sheet2").Cells(j, 2).Value = Worksheets("Sheet1").Cells(y, 1).Value Then Worksheets("Sheet3").Cells(j, 4).Value = Worksheets("Sheet1").Cells(y, 3).Value Else End If Case "Science" If Worksheets("Sheet2").Cells(j, 2).Value = Worksheets("Sheet1").Cells(y, 1).Value Then Worksheets("Sheet3").Cells(j, 5).Value = Worksheets("Sheet1").Cells(y, 4).Value Else End If Case Else End Select Next y Next i Next j End Sub 'Sharon1()

and without confusing comments!

Code:

Sub Sharon1()

Dim UsedRange1 As Range
Set UsedRange1 = Worksheets("Sheet1").Range("A1").CurrentRegion
Dim UsedRange1Row As Byte, UsedRange2Row As Byte
Let UsedRange1Row = UsedRange1.Rows.Count
Dim UsedRange2 As Range
Set UsedRange2 = Worksheets("Sheet2").Range("A1").CurrentRegion
Let UsedRange2Row = UsedRange2.Rows.Count


For y = 2 To UsedRange2Row Step 1
  Worksheets("Sheet3").Cells(y, 1).Value = Worksheets("Sheet2").Cells(y, 1).Value
  Worksheets("Sheet3").Cells(y, 2).Value = Worksheets("Sheet2").Cells(y, 2).Value
Next y


For j = 2 To UsedRange2Row
    For i = 3 To 5
      For y = 2 To UsedRange1Row
        Select Case Worksheets("Sheet2").Cells(j, i).Value
          Case "Maths"
              If Worksheets("Sheet2").Cells(j, 2).Value = Worksheets("Sheet1").Cells(y, 1).Value Then
                 Worksheets("Sheet3").Cells(j, 3).Value = Worksheets("Sheet1").Cells(y, 2).Value
              Else
              End If
          Case "History"
              If Worksheets("Sheet2").Cells(j, 2).Value = Worksheets("Sheet1").Cells(y, 1).Value Then
                 Worksheets("Sheet3").Cells(j, 4).Value = Worksheets("Sheet1").Cells(y, 3).Value
              Else
              End If
          Case "Science"
              If Worksheets("Sheet2").Cells(j, 2).Value = Worksheets("Sheet1").Cells(y, 1).Value Then
                 Worksheets("Sheet3").Cells(j, 5).Value = Worksheets("Sheet1").Cells(y, 4).Value
              Else
              End If
          Case Else
        End Select
      Next y
    Next i
  Next j
End Sub

sharan250 · Aug 12, 2014

Hi hiker95,

Thanks a lot for your solution. It works.

hiker95 said:
sharan250,
The following is based on you text displays, and, my screenshots. If there will be more than 3 subjects, I will have to adjust the macro.

sharan250 · Aug 12, 2014

Hi DocAEstein,

Thank you for your solution. I tried it and it works. Marks of the respective subjects are copied in appropriate cells, However, subject name at the top of the cell is not copied from sheet1 to sheet3. I hope I can fix that

Cheers,

hiker95 · Aug 12, 2014

sharan250,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

If you ever have more than 3 subjects, let me know, because I do have a macro that will handle more than 3 subjects.

You will have to supply screenshots of the two raw data worksheets so that I can test the macro with live data.

DocAElstein · Aug 13, 2014

sharan250 said:
Hi DocAEstein,

Thank you for your solution. ..........

......Cheers,

Hi,
. Youre welcome.

sharan250 said:
Marks of the respective subjects are copied in appropriate cells, However, subject name at the top of the cell is not copied from sheet1 to sheet3. I hope I can fix that

Cheers,

. As a beginner I still tend to Type in things like Headings manually, as I did in the File at Filesnack that I did for you.
. But it is fairly straight forward.
. Here the code again which also writes in the headings:

Sub Sharon1() Dim UsedRange1 As Range 'Give your usedrange for table1 a name and allow it to have all properties and methods of a range object Set UsedRange1 = Worksheets("Sheet1").Range("A1").CurrentRegion ' Currentrange property returns the table area. Dim UsedRange1Row As Byte, UsedRange2Row As Byte ' limit tables to 255 rows Let UsedRange1Row = UsedRange1.Rows.Count 'This gives you how many rows in table1 that you have Dim UsedRange2 As Range Set UsedRange2 = Worksheets("Sheet2").Range("A1").CurrentRegion Let UsedRange2Row = UsedRange2.Rows.Count Dim UsedRange1Column As Byte 'Limit columns to 255 Let UsedRange1Column = UsedRange1.Columns.Count 'This returns the used Columns in Table 1 For y = 2 To UsedRange2Row Step 1 'For every row in table 2... Worksheets("Sheet3").Cells(y, 1).Value = Worksheets("Sheet2").Cells(y, 1).Value ' ...Put ID in column 1, .. Worksheets("Sheet3").Cells(y, 2).Value = Worksheets("Sheet2").Cells(y, 2).Value ' ... Put #Unit test in column 2 Next y For x = 1 To 2 ' For Subject Headings starting at column 1 in Sheet3.... Worksheets("Sheet3").Cells(1, x).Value = Worksheets("Sheet2").Cells(1, x).Value '....bring thes in from Sheet 2 Next x For x = 3 To UsedRange1Column + 1 ' For Id uand Unit test Headings starting at column 3 in Sheet3.... Worksheets("Sheet3").Cells(1, x).Value = Worksheets("Sheet2").Cells(2, x).Value '....bring thes in from Sheet 2 Next x For j = 2 To UsedRange2Row 'for each row in sheet 2... For i = 3 To 5 '...go through column 3,4 and 5.... For y = 2 To UsedRange1Row '... and go through every row in Sheet 1... Select Case Worksheets("Sheet2").Cells(j, i).Value ' ..then look for Maths, History, or Science Case "Maths" If Worksheets("Sheet2").Cells(j, 2).Value = Worksheets("Sheet1").Cells(y, 1).Value Then ' If maths then Worksheets("Sheet3").Cells(j, 3).Value = Worksheets("Sheet1").Cells(y, 2).Value ' put appropriate value in column 3, sheet5...etc Else End If Case "History" If Worksheets("Sheet2").Cells(j, 2).Value = Worksheets("Sheet1").Cells(y, 1).Value Then Worksheets("Sheet3").Cells(j, 4).Value = Worksheets("Sheet1").Cells(y, 3).Value Else End If Case "Science" If Worksheets("Sheet2").Cells(j, 2).Value = Worksheets("Sheet1").Cells(y, 1).Value Then Worksheets("Sheet3").Cells(j, 5).Value = Worksheets("Sheet1").Cells(y, 4).Value Else End If Case Else End Select Next y Next i Next j End Sub 'Sharon1()

The important extra lines are:

For y = 2 To UsedRange2Row Step 1 'For every row in table 2... Worksheets("Sheet3").Cells(y, 1).Value = Worksheets("Sheet2").Cells(y, 1).Value ' ...Put ID in column 1, .. Worksheets("Sheet3").Cells(y, 2).Value = Worksheets("Sheet2").Cells(y, 2).Value ' ... Put #Unit test in column 2 Next y For x = 1 To 2 ' For Subject Headings starting at column 1 in Sheet3.... Worksheets("Sheet3").Cells(1, x).Value = Worksheets("Sheet2").Cells(1, x).Value '....bring thes in from Sheet 2 Next x

again without comments etc..

Code:

For x = 1 To 2  Worksheets("Sheet3").Cells(1, x).Value = Worksheets("Sheet2").Cells(1, x).Value
Next x


For x = 3 To UsedRange1Column + 1
  Worksheets("Sheet3").Cells(1, x).Value = Worksheets("Sheet2").Cells(2, x).Value
Next x

Alan

P.s. In the practice I would tend to go for the hiker95 code - It is a professional solution and probably works more efficiently etc. Mine can possibly help a bit at the beginner stage to see a bit better wot is going on, and I tend to use the basics as I Don't know the clever stuff yet!

Compare two sheets and copy match value to new sheet

sharan250

New Member

Excel Facts

hiker95

Well-known Member

sharan250

New Member

hiker95

Well-known Member

DocAElstein

Banned user

DocAElstein

Banned user

sharan250

New Member

sharan250

New Member

hiker95

Well-known Member

DocAElstein

Banned user

Similar threads

Forum statistics

Share this page

Compare two sheets and copy match value to new sheet

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

Banned user

Banned user

New Member

New Member

Well-known Member

Banned user

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