Compare two sheets and copy match value to new sheet

[sharan250]

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]

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]

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:

ID	Unit test	Maths	History	Science
1231	1	225	264	242
1232	3		270	275
1233	5	215		321
1234	5	215	243
1235	2			241
1236	7		118
1237	8	40		99

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Maths value is the sum of unit test marks 1-4 in maths and so on.

Unit test	Maths
1	70
2	60
3	50
4	75

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

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,

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]

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]

Hi hiker95,

I apologize for my mistake.

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,

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]

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]

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

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Excel 2007

	A	B	C	D	E	F	G	H	I	J	K
1	ID	#Unit test	Maths	History	Science
2	1231	1	225	264	242
3	1232	3		270	275
4	1233	5	215		321
5	1234	5	215	243
6	1235	2			241
7	1236	7		118
8	1237	8	40		99
9

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

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

 

[hiker95]

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]

Hi Hiker95,

Please find below logic for other data.

Sample raw data worksheets:

Excel 2007: Sheet 1

	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: Sheet 2

	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​

Excel 2007: Sheet 3

Sheet3: ID 1231

	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

<tbody>
</tbody>

Sheet3​

Sheet3: ID 1232

	A	B	C	D	E	F	G	H
1	ID	#Unit test	Maths	History	Science			Unit test History	Unit test Science
2	1231	1	225	264	242			3 85	3 33
3	1232	3		270	275			4 60	4 62
4	1233	5	215		321			5 70	5 94
5	1234	5	215	243				6 55	6 86
6	1235	2			241			270	275
7	1236	7		118
8	1237	8	40		99
9

<tbody>
</tbody>

​

Sheet3: ID 1233 same as above
Sheet3: ID 1236

1	ID	#Unit test	Maths	History	Science			Unit test History
2	1231	1	225	264	242			7 58
3	1232	3		270	275			8 60
4	1233	5	215		321			9 0
5	1234	5	215	243				10 0
6	1235	2			241			118
7	1236	7		118
8	1237	8	40		99
9

<tbody>
</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

1	ID	#Unit test	Maths	History	Science			Unit test Maths	Unit test Science
2	1231	1	225	264	242			8 40	8 99
3	1232	3		270	275			9 0	9 0
4	1233	5	215		321			10 0	10 0
5	1234	5	215	243				11 0	11 0
6	1235	2			241			40	99
7	1236	7		118
8	1237	8	40		99
9

<tbody>
</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]

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.