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 13, 2014

DocAElstein,

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.

Thank you very much.

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!

Practice, practice, and, more of the same.

sharan250 · Aug 13, 2014

Hi hiker95,

Thanks for your support.
I was working with your code and was trying to sum dynamic range for matching #unit test for a subject. Example: Lets say after matching #unit test from sheet2 with sheet1 for a specific subject (e.g. #unit test in sheet2 is 1 and Subject is Maths), then I need to add maths marks from Unit test 1 to 4 in sheet1 and put the sun value in sheet3 at corresponding value. Sheet3 should appear as:
[TABLE="width: 245"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]Unit test[/TD]
[TD]Maths[/TD]
[TD]History[/TD]
[TD]Science[/TD]
[/TR]
[TR]
[TD="align: right"]1231[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]225[/TD]
[TD="align: right"]264[/TD]
[TD="align: right"]242[/TD]
[/TR]
[TR]
[TD="align: right"]1232[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]270[/TD]
[TD="align: right"]275[/TD]
[/TR]
[TR]
[TD="align: right"]1233[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]215[/TD]
[TD][/TD]
[TD="align: right"]321[/TD]
[/TR]
[TR]
[TD="align: right"]1234[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]215[/TD]
[TD="align: right"]243[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1235[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]241[/TD]
[/TR]
[TR]
[TD="align: right"]1236[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="align: right"]118[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1237[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]40[/TD]
[TD][/TD]
[TD="align: right"]99[/TD]
[/TR]
</tbody>[/TABLE]

Maths value is the sum of unit test marks 1-4 in maths and so on.
[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Unit test[/TD]
[TD="width: 64"]Maths[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]70[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]75[/TD]
[/TR]
</tbody>[/TABLE]

I tried to work with your code by replacing

w3.Cells(d.Row, s.Column + 1).Value = w1.Cells(u.Row, s.Column).Value

with

w3.Cells(d.Row, s.Column + 1).Value = w1function.Sum(Range(Cells(u.Row, s.Column).Address, Cells(u.Row + 3, s.Column + 3).Address))

However, as expected it didnt work.

Further, to add more subjects example economics, I made minor changes to the code as below and it works fine. However, I would like to know is there any way by which we can make it dynamic (i.e. if the subjects are not fixed)

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(, 4).Value = Array("Maths", "History", "Science", "Economics")
'Replaced Resize(, 3) with Resize(, 4)
.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 & ":F" & d.Row)
'Replaced E with F
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

Thanks for all you support.

Cheers,

hiker95 said:
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.

hiker95 · Aug 13, 2014

sharan250,

I can not understand what your new requirements are.

Flat text displays will not work.

It would probably be better for me if you were to use the MrExcel HTML Maker, or, Excel Jeanie, to display your worksheets, before, and, after.

To post your data, you can download and install one of the following two programs:
1. MrExcel HTMLMaker20101230
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Installation instructions here:
http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970

2. Excel Jeanie
Download

If you can not install either of the above products, then:

You can upload your workbook to Box Net,

sensitive data changed

mark the workbook for sharing

and provide us with a link to your workbook.

sharan250 · Aug 13, 2014

Hi hiker95,

I apologize for my mistake.

hiker95 said:
sharan250,

I can not understand what your new requirements are.

Flat text displays will not work.

From Sheet2, I identify the Unit test number and match it with unit test number of sheet1. After identifying the correct row and column in sheet1, I want to add all the marks of that subject for next 4 unit test.
Sheet2:
https://app.box.com/s/y2r5nvwchmiqd273spwb

Sheet1:

https://app.box.com/s/78hx1gwb30fb2fygrgy9

In case of sheet2, where ID is 1231, Unit test is 1, and subject is Maths (Row2), I need to go to sheet1 and sum the values of cells B2:B5 and same goes for others as well to get the result as sheet3 https://app.box.com/s/17f9yrswdzrqeog5qju4

Further, I have attached example workbook at https://app.box.com/s/p5nwnxmnc5tsal2n6sgn

I hope I am bit clear now and can get your help. Many thanks for your patience and help.

Cheers,

hiker95 said:
You can upload your workbook to Box Net,

sensitive data changed

mark the workbook for sharing

and provide us with a link to your workbook.

hiker95 · Aug 13, 2014

sharan250,

The fist two files on BOX contain gif files/graphics?

You are posting pictures/graphics. This means that if this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually. That makes no sense.

The third file on BOX returned the following message:

Excel found unreadable content in 'test - Copy (1).xlsx'.

Do you want to recover the contents of this workbook?

If you trust the source of this workbook, click Yes No

What I requested was for one workbook with all the worksheets?????

sharan250 · Aug 13, 2014

Hi hiker95,

I am extremely sorry for the inconvenience. I have written the sheets value below. Also have hyperlinked an excel sheet with the values. Hope you would be able to access it.
Many thanks for your help.

Excel 2007

	A	B	C
#Unit test	Maths	History	Science

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]95[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]52[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]33[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]62[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]94[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]86[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]41[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]99[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1

Excel 2007

	A	B
ID	#Unit test
Maths	History	Science
History	Science
Science	Maths
History	Maths
Science
History
Maths	Science

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1231[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1232[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1233[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1234[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1235[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1236[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1237[/TD]
[TD="align: right"]8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2

Excel 2007

	A	B	C	D
ID	#Unit test	Maths	History	Science

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1231[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]225[/TD]
[TD="align: right"]264[/TD]
[TD="align: right"]242[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1232[/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: #C5D9F1, align: right"][/TD]
[TD="bgcolor: #C5D9F1, align: right"]270[/TD]
[TD="align: right"]275[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1233[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]215[/TD]
[TD="bgcolor: #C5D9F1, align: right"][/TD]
[TD="align: right"]321[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1234[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]215[/TD]
[TD="align: right"]243[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1235[/TD]
[TD="align: right"]2[/TD]
[TD="bgcolor: #C5D9F1, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]241[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1236[/TD]
[TD="align: right"]7[/TD]
[TD="bgcolor: #C5D9F1, align: right"][/TD]
[TD="align: right"]118[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1237[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]40[/TD]
[TD="bgcolor: #C5D9F1, align: right"][/TD]
[TD="align: right"]99[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3

http://app.box.com/s/u0to8firl3b1ef2jepy9

hiker95 · Aug 14, 2014

sharan250,

Thanks for the new screenshots, and, the workbook.

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

I understand the logic for the YELLOW results, but, I do not understand the logic for the rest of the results on Sheet3:

Excel 2007

A

B

C

D

E

F

G

H

1

ID

#Unit test

Maths

History

Science

Unit test Maths

2

1231

1

225

264

242

1 70

3

1232

3

270

275

2 60

4

1233

5

215

321

3 50

5

1234

5

215

243

4 75

6

1235

2

241

225

7

1236

7

118

8

1237

8

40

99

9

Sheet3

Please explain in detail for the rest of the numbers.

sharan250 · Aug 14, 2014

Hi Hiker95,

Please find below logic for other data.

Sample raw data worksheets:

Excel 2007: Sheet 1

	A	B	C	D

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]#Unit test[/TD]
[TD="bgcolor: #FFFFFF"]Maths[/TD]
[TD="bgcolor: #FFFFFF"]History[/TD]
[TD="bgcolor: #FFFFFF"]Science[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="bgcolor: #FFFF00, align: right"]70[/TD]
[TD="bgcolor: #FFFF00, align: right"]55[/TD]
[TD="bgcolor: #FFFF00, align: right"]95[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="bgcolor: #FFFF00, align: right"]60[/TD]
[TD="bgcolor: #FFFF00, align: right"]64[/TD]
[TD="bgcolor: #FFFF00, align: right"]52[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF, align: right"]3[/TD]
[TD="bgcolor: #FFFF00, align: right"]50[/TD]
[TD="bgcolor: #FFFF00, align: right"]85[/TD]
[TD="bgcolor: #FFFF00, align: right"]33[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4[/TD]
[TD="bgcolor: #FFFF00, align: right"]75[/TD]
[TD="bgcolor: #FFFF00, align: right"]60[/TD]
[TD="bgcolor: #FFFF00, align: right"]62[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FFFFFF, align: right"]5[/TD]
[TD="bgcolor: #FFFFFF, align: right"]65[/TD]
[TD="bgcolor: #FFFFFF, align: right"]70[/TD]
[TD="bgcolor: #FFFFFF, align: right"]94[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FFFFFF, align: right"]6[/TD]
[TD="bgcolor: #FFFFFF, align: right"]40[/TD]
[TD="bgcolor: #FFFFFF, align: right"]55[/TD]
[TD="bgcolor: #FFFFFF, align: right"]86[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FFFFFF, align: right"]7[/TD]
[TD="bgcolor: #FFFFFF, align: right"]70[/TD]
[TD="bgcolor: #FFFFFF, align: right"]58[/TD]
[TD="bgcolor: #FFFFFF, align: right"]41[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FFFFFF, align: right"]8[/TD]
[TD="bgcolor: #FFFFFF, align: right"]40[/TD]
[TD="bgcolor: #FFFFFF, align: right"]60[/TD]
[TD="bgcolor: #FFFFFF, align: right"]99[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

</tbody>

Sheet1

Excel 2007: Sheet 2

	A	B	C	D	E

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]ID[/TD]
[TD="bgcolor: #FFFFFF"]#Unit test[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1231[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="bgcolor: #FFFFFF"]Maths[/TD]
[TD="bgcolor: #FFFFFF"]History[/TD]
[TD="bgcolor: #FFFFFF"]Science[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1232[/TD]
[TD="bgcolor: #FFFFFF, align: right"]3[/TD]
[TD="bgcolor: #FFFFFF"]History[/TD]
[TD="bgcolor: #FFFFFF"]Science[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1233[/TD]
[TD="bgcolor: #FFFFFF, align: right"]5[/TD]
[TD="bgcolor: #FFFFFF"]Science[/TD]
[TD="bgcolor: #FFFFFF"]Maths[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1234[/TD]
[TD="bgcolor: #FFFFFF, align: right"]5[/TD]
[TD="bgcolor: #FFFFFF"]History[/TD]
[TD="bgcolor: #FFFFFF"]Maths[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1235[/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="bgcolor: #FFFFFF"]Science[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1236[/TD]
[TD="bgcolor: #FFFFFF, align: right"]7[/TD]
[TD="bgcolor: #FFFFFF"]History[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1237[/TD]
[TD="bgcolor: #FFFFFF, align: right"]8[/TD]
[TD="bgcolor: #FFFFFF"]Maths[/TD]
[TD="bgcolor: #FFFFFF"]Science[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2

Excel 2007: Sheet 3

Sheet3: ID 1231

	A	B	C	D	E	F	G	H
Unit test Maths
1 70
2 60
3 50
4 75

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]ID[/TD]
[TD="bgcolor: #FFFFFF"]#Unit test[/TD]
[TD="bgcolor: #FFFFFF"]Maths[/TD]
[TD="bgcolor: #FFFFFF"]History[/TD]
[TD="bgcolor: #FFFFFF"]Science[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1231[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="bgcolor: #FFFF00, align: right"]225[/TD]
[TD="bgcolor: #FFFF00, align: right"]264[/TD]
[TD="bgcolor: #FFFF00, align: right"]242[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1232[/TD]
[TD="bgcolor: #FFFFFF, align: right"]3[/TD]
[TD="bgcolor: #C5D9F1, align: right"][/TD]
[TD="bgcolor: #C5D9F1, align: right"]270[/TD]
[TD="bgcolor: #FFFFFF, align: right"]275[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1233[/TD]
[TD="bgcolor: #FFFFFF, align: right"]5[/TD]
[TD="bgcolor: #FFFFFF, align: right"]215[/TD]
[TD="bgcolor: #C5D9F1, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]321[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1234[/TD]
[TD="bgcolor: #FFFFFF, align: right"]5[/TD]
[TD="bgcolor: #FFFFFF, align: right"]215[/TD]
[TD="bgcolor: #FFFFFF, align: right"]243[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1235[/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="bgcolor: #C5D9F1, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]241[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"]225[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1236[/TD]
[TD="bgcolor: #FFFFFF, align: right"]7[/TD]
[TD="bgcolor: #C5D9F1, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]118[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1237[/TD]
[TD="bgcolor: #FFFFFF, align: right"]8[/TD]
[TD="bgcolor: #FFFFFF, align: right"]40[/TD]
[TD="bgcolor: #C5D9F1, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]99[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3

Sheet3: ID 1232

	A	B	C	D	E	F
ID	#Unit test	Maths	History	Science	Unit test History	Unit test Science
3 85	3 33
4 60	4 62
5 70	5 94
6 55	6 86

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1231[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: #FFFF00, align: right"]225[/TD]
[TD="bgcolor: #FFFF00, align: right"]264[/TD]
[TD="bgcolor: #FFFF00, align: right"]242[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1232[/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: #C5D9F1, align: right"][/TD]
[TD="bgcolor: #C5D9F1, align: right"]270[/TD]
[TD="align: right"]275[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1233[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]215[/TD]
[TD="bgcolor: #C5D9F1, align: right"][/TD]
[TD="align: right"]321[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1234[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]215[/TD]
[TD="align: right"]243[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1235[/TD]
[TD="align: right"]2[/TD]
[TD="bgcolor: #C5D9F1, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]241[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"]270[/TD]
[TD="bgcolor: #FFFF00, align: right"]275[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1236[/TD]
[TD="align: right"]7[/TD]
[TD="bgcolor: #C5D9F1, align: right"][/TD]
[TD="align: right"]118[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1237[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]40[/TD]
[TD="bgcolor: #C5D9F1, align: right"][/TD]
[TD="align: right"]99[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3: ID 1233 same as above
Sheet3: ID 1236

ID	#Unit test	Maths	History	Science	Unit test History
7 58
8 60
9 0
10 0

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1231[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: #FFFF00, align: right"]225[/TD]
[TD="bgcolor: #FFFF00, align: right"]264[/TD]
[TD="bgcolor: #FFFF00, align: right"]242[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1232[/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: #C5D9F1, align: right"][/TD]
[TD="bgcolor: #C5D9F1, align: right"]270[/TD]
[TD="align: right"]275[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1233[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]215[/TD]
[TD="bgcolor: #C5D9F1, align: right"][/TD]
[TD="align: right"]321[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1234[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]215[/TD]
[TD="align: right"]243[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1235[/TD]
[TD="align: right"]2[/TD]
[TD="bgcolor: #C5D9F1, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]241[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"]118[/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1236[/TD]
[TD="align: right"]7[/TD]
[TD="bgcolor: #C5D9F1, align: right"][/TD]
[TD="align: right"]118[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1237[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]40[/TD]
[TD="bgcolor: #C5D9F1, align: right"][/TD]
[TD="align: right"]99[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]

</tbody>

For ID 1236, we have only 2 values, so we add two zeros for next two values i.e. unit tests 9 and 10 (which do not exist) becomes 0 (zero).

Sheet3: ID 1237

ID	#Unit test	Maths	History	Science	Unit test Maths	Unit test Science
8 40	8 99
9 0	9 0
10 0	10 0
11 0	11 0

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1231[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: #FFFF00, align: right"]225[/TD]
[TD="bgcolor: #FFFF00, align: right"]264[/TD]
[TD="bgcolor: #FFFF00, align: right"]242[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1232[/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: #C5D9F1, align: right"][/TD]
[TD="bgcolor: #C5D9F1, align: right"]270[/TD]
[TD="align: right"]275[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1233[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]215[/TD]
[TD="bgcolor: #C5D9F1, align: right"][/TD]
[TD="align: right"]321[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1234[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]215[/TD]
[TD="align: right"]243[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1235[/TD]
[TD="align: right"]2[/TD]
[TD="bgcolor: #C5D9F1, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]241[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"]40[/TD]
[TD="bgcolor: #FFFF00, align: right"]99[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1236[/TD]
[TD="align: right"]7[/TD]
[TD="bgcolor: #C5D9F1, align: right"][/TD]
[TD="align: right"]118[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1237[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]40[/TD]
[TD="bgcolor: #C5D9F1, align: right"][/TD]
[TD="align: right"]99[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

For ID 1237, we have only 1 value, so we add three zeros for next three values i.e. unit tests 9, 10 and 11 (which do not exist) becomes 0 (zero).

I hope, I am clear now. Looking forward to your help. Many thanks for all your help.

Cheers,

hiker95 · Aug 14, 2014

sharan250,

Sorry, I do not understand.

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

Compare two sheets and copy match value to new sheet

sharan250

New Member

hiker95

Well-known Member

sharan250

New Member

hiker95

Well-known Member

sharan250

New Member

hiker95

Well-known Member

sharan250

New Member

hiker95

Well-known Member

sharan250

New Member

hiker95

Well-known Member

Similar threads

Share this page

Compare two sheets and copy match value to new sheet

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

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