Compare two sheets using match/find

[Azurai]

Hi all..

Glad to know this forum..
I have problem with my macro excel.

I want to compare 2 sheets (1st Semester & 2nd Semester), the result is in the Sheet Output. My problem is there is possibilities that there are some values have duplicates. For example, there are 2 "Budi" Grade "7" in Sheet1 and Sheet2 but have different score.
Currently, I am using find codes to match the row from Sheet1 to Sheet2 with the condition if the row have the same "Name" and "Grade", they will be on the same row in the Sheet Output. The problem is if there is duplicate value of "Name" and "Grade", like the case of "Budi" Grade "7", the first match is being overwrite with the second match. I want the result is exactly just like in the Sheet Output without deleting any duplicates.

Here is my data:

Book1.xlsm
	A	B	C	D	E
1	Name	Grade	Math	Biology	Physics
2	Ammar	7	83	38	7
3	Agus	8	11	64	24
4	Budi	7	62	21	8
5	Budi	7	31	77	5
6	Budi	8	57	83	30
7	Budi	9	43	79	67
8	Bambang	7	5	76	72
9	Bambang	8	4	82	75
10	Bambang	9	83	96	68
11	Eli	8	95	30	95
12	Franda	8	31	99	92
13	Gladisya	7	8	22	36
14	Gladisya	9	43	53	52
15	Haris	8	34	73	86
16	Lina	9	82	14	43
17	Zoey	8	9	70	78
1st Semester

Book1.xlsm
	A	B	C	D	E
1	Name	Grade	Math	Biology	Physics
2	Ammar	7	22	66	84
3	Budi	7	21	83	63
4	Budi	7	30	7	78
5	Budi	8	61	66	31
6	Budi	9	81	20	92
7	Bambang	7	23	70	89
8	Bambang	8	42	89	9
9	Bambang	9	28	50	64
10	Eli	7	7	65	13
11	Eli	8	38	6	64
12	Franda	8	98	26	89
13	Gladisya	7	98	98	75
14	Gladisya	9	94	33	4
15	Lina	9	20	46	10
16	Zoey	8	68	56	63
2nd Semester

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Grade	Math	Biology	Physics			Name	Grade	Math	Biology	Physics
2	Ammar	7	83	38	7			Ammar	7	22	66	84
3	Agus	8	11	64	24
4	Budi	7	62	21	8			Budi	7	21	83	63
5	Budi	7	31	77	5			Budi	7	30	7	78
6	Budi	8	57	83	30			Budi	8	61	66	31
7	Budi	9	43	79	67			Budi	9	81	20	92
8	Bambang	7	5	76	72			Bambang	7	23	70	89
9	Bambang	8	4	82	75			Bambang	8	42	89	9
10	Bambang	9	83	96	68			Bambang	9	28	50	64
11								Eli	7	7	65	13
12	Eli	8	95	30	95			Eli	8	38	6	64
13	Franda	8	31	99	92			Franda	8	98	26	89
14	Gladisya	7	8	22	36			Gladisya	7	98	98	75
15	Gladisya	9	43	53	52			Gladisya	9	94	33	4
16	Haris	8	34	73	86
17	Lina	9	82	14	43			Lina	9	20	46	10
18	Zoey	8	9	70	78			Zoey	8	68	56	63
Output

Thank you for your help. I am really appreciated..

 

[sandy666]

like this ?

Name	Grade	Math	Biology	Physics	Table2.Name	Table2.Grade	Table2.Math	Table2.Biology	Table2.Physics
Ammar	7	83	38	7	Ammar	7	22	66	84
Agus	8	11	64	24
Budi	7	62	21	8	Budi	7	21	83	63
Budi	7	62	21	8	Budi	7	30	7	78
Budi	7	31	77	5	Budi	7	21	83	63
Budi	7	31	77	5	Budi	7	30	7	78
Budi	8	57	83	30	Budi	8	61	66	31
Budi	9	43	79	67	Budi	9	81	20	92
Bambang	7	5	76	72	Bambang	7	23	70	89
Bambang	8	4	82	75	Bambang	8	42	89	9
Bambang	9	83	96	68	Bambang	9	28	50	64
Eli	8	95	30	95	Eli	8	38	6	64
Franda	8	31	99	92	Franda	8	98	26	89
Gladisya	7	8	22	36	Gladisya	7	98	98	75
Gladisya	9	43	53	52	Gladisya	9	94	33	4
Haris	8	34	73	86
Lina	9	82	14	43	Lina	9	20	46	10
Zoey	8	9	70	78	Zoey	8	68	56	63
					Eli	7	7	65	13

 

[Azurai]

Hi @sandy666..

Thanks for the response..

But I want the result exactly like this, just match the row which have the same "Name" and "Grade", without changing the data from Sheet1 and Sheet2:

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Grade	Math	Biology	Physics			Name	Grade	Math	Biology	Physics
2	Ammar	7	83	38	7			Ammar	7	22	66	84
3	Agus	8	11	64	24
4	Budi	7	62	21	8			Budi	7	21	83	63
5	Budi	7	31	77	5			Budi	7	30	7	78
6	Budi	8	57	83	30			Budi	8	61	66	31
7	Budi	9	43	79	67			Budi	9	81	20	92
8	Bambang	7	5	76	72			Bambang	7	23	70	89
9	Bambang	8	4	82	75			Bambang	8	42	89	9
10	Bambang	9	83	96	68			Bambang	9	28	50	64
11								Eli	7	7	65	13
12	Eli	8	95	30	95			Eli	8	38	6	64
13	Franda	8	31	99	92			Franda	8	98	26	89
14	Gladisya	7	8	22	36			Gladisya	7	98	98	75
15	Gladisya	9	43	53	52			Gladisya	9	94	33	4
16	Haris	8	34	73	86
17	Lina	9	82	14	43			Lina	9	20	46	10
18	Zoey	8	9	70	78			Zoey	8	68	56	63
Output

Thank you..

 

[Akuini]

Try this:
Copy data in both sheet to the sheet Output (to col A & H) then run this code:

Sub a1124363b()
'https://www.mrexcel.com/board/threads/compare-two-sheets-using-match-find.1124363/
Dim a As Range, b As Range
Dim n As Long, i As Long
Application.ScreenUpdating = False
n = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To 2 * n
    Set a = Cells(i, "A")
    Set b = Cells(i, "H")
    If Len(a) = 0 And Len(b) = 0 Then Exit Sub
        If Len(a) <> 0 And Len(b) <> 0 Then
            If a.Value < b.Value Then
                b.Resize(1, 5).Insert
            ElseIf a.Value > b.Value Then
                a.Resize(1, 5).Insert
            ElseIf a.Value = b.Value Then
                If a.Offset(, 1).Value < b.Offset(, 1).Value Then
                b.Resize(1, 5).Insert
                ElseIf a.Offset(, 1).Value > b.Offset(, 1).Value Then
                a.Resize(1, 5).Insert
                End If
            End If
        End If
Next
Application.ScreenUpdating = True
End Sub

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Grade	Math	Biology	Physics			Name	Grade	Math	Biology	Physics
2	Ammar	7	82.84841	37.86475	6.964003			Ammar	7	21.64469	65.8258	84.46054
3	Agus	8	11.14409	64.1461	23.60417
4	Budi	7	61.83003	21.43611	7.712235			Budi	7	20.71858	82.96265	62.99988
5	Budi	7	30.93253	76.73572	4.958872			Budi	7	29.50738	7.013686	78.34777
6	Budi	8	57.49479	82.59883	29.55132			Budi	8	61.46952	66.1809	31.02363
7	Budi	9	43.46295	79.42116	67.09739			Budi	9	81.11292	20.24594	91.73688
8	Bambang	7	5.172003	76.1827	71.78864			Bambang	7	22.81528	69.81972	88.74699
9	Bambang	8	3.692272	81.69376	74.72925			Bambang	8	41.71516	88.58337	9.382638
10	Bambang	9	82.97577	96.4961	68.20357			Bambang	9	27.68416	50.06342	64.42479
11								Eli	7	6.921891	65.3676	12.99684
12	Eli	8	95.20385	30.02022	94.55375			Eli	8	38.44799	6.043297	64.0684
13	Franda	8	31.1817	99.14596	92.15863			Franda	8	97.5516	25.52623	89.15507
14	Gladisya	7	8.056407	22.00313	35.57952			Gladisya	7	97.58434	98.39059	74.56842
15	Gladisya	9	43.46564	52.73536	51.57634			Gladisya	9	93.72834	32.8932	4.156494
16	Haris	8	34.01112	72.81666	85.90114
17	Lina	9	81.54547	14.44405	43.05643			Lina	9	20.08013	46.31668	9.738968
18	Zoey	8	8.911537	70.05261	78.01206			Zoey	8	68.4934	55.79737	62.90382
Sheet2

 

[sandy666]

without changing the data from Sheet1 and Sheet2:

data has not been changed, maybe layout

 

[Azurai]

Try this:
Copy data in both sheet to the sheet Output (to col A & H) then run this code:

Sub a1124363b()
'https://www.mrexcel.com/board/threads/compare-two-sheets-using-match-find.1124363/
Dim a As Range, b As Range
Dim n As Long, i As Long
Application.ScreenUpdating = False
n = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To 2 * n
    Set a = Cells(i, "A")
    Set b = Cells(i, "H")
    If Len(a) = 0 And Len(b) = 0 Then Exit Sub
        If Len(a) <> 0 And Len(b) <> 0 Then
            If a.Value < b.Value Then
                b.Resize(1, 5).Insert
            ElseIf a.Value > b.Value Then
                a.Resize(1, 5).Insert
            ElseIf a.Value = b.Value Then
                If a.Offset(, 1).Value < b.Offset(, 1).Value Then
                b.Resize(1, 5).Insert
                ElseIf a.Offset(, 1).Value > b.Offset(, 1).Value Then
                a.Resize(1, 5).Insert
                End If
            End If
        End If
Next
Application.ScreenUpdating = True
End Sub

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Grade	Math	Biology	Physics			Name	Grade	Math	Biology	Physics
2	Ammar	7	82.84841	37.86475	6.964003			Ammar	7	21.64469	65.8258	84.46054
3	Agus	8	11.14409	64.1461	23.60417
4	Budi	7	61.83003	21.43611	7.712235			Budi	7	20.71858	82.96265	62.99988
5	Budi	7	30.93253	76.73572	4.958872			Budi	7	29.50738	7.013686	78.34777
6	Budi	8	57.49479	82.59883	29.55132			Budi	8	61.46952	66.1809	31.02363
7	Budi	9	43.46295	79.42116	67.09739			Budi	9	81.11292	20.24594	91.73688
8	Bambang	7	5.172003	76.1827	71.78864			Bambang	7	22.81528	69.81972	88.74699
9	Bambang	8	3.692272	81.69376	74.72925			Bambang	8	41.71516	88.58337	9.382638
10	Bambang	9	82.97577	96.4961	68.20357			Bambang	9	27.68416	50.06342	64.42479
11								Eli	7	6.921891	65.3676	12.99684
12	Eli	8	95.20385	30.02022	94.55375			Eli	8	38.44799	6.043297	64.0684
13	Franda	8	31.1817	99.14596	92.15863			Franda	8	97.5516	25.52623	89.15507
14	Gladisya	7	8.056407	22.00313	35.57952			Gladisya	7	97.58434	98.39059	74.56842
15	Gladisya	9	43.46564	52.73536	51.57634			Gladisya	9	93.72834	32.8932	4.156494
16	Haris	8	34.01112	72.81666	85.90114
17	Lina	9	81.54547	14.44405	43.05643			Lina	9	20.08013	46.31668	9.738968
18	Zoey	8	8.911537	70.05261	78.01206			Zoey	8	68.4934	55.79737	62.90382
Sheet2

Hi.. Thank you for your script. It works for that kind of data.
I want to ask further question. What if the data is just like this:

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Grade	Math	Biology	Physics			Name	Grade	Math	Biology	Physics
2	Ammar	7	83	38	7			Ammar	7	83	38	7
3	Agus	8	11	64	24			Budi	7	21	83	63
4	Budi	7	62	21	8			Budi	7	30	7	78
5	Budi	8	57	83	30			Budi	8	61	66	31
6	Budi	9	43	79	67			Budi	9	81	20	92
7	Bambang	7	5	76	72			Bambang	7	23	70	89
8	Bambang	8	4	82	75			Bambang	8	42	89	9
9	Bambang	9	83	96	68			Bambang	9	28	50	64
10	Budi	7	31	77	5			Eli	7	7	65	13
11	Eli	8	95	30	95			Eli	8	38	6	64
12	Franda	8	31	99	92			Franda	8	98	26	89
13	Gladisya	7	8	22	36			Gladisya	7	98	98	75
14	Haris	8	34	73	86			Gladisya	9	94	33	4
15	Zoey	8	9	70	78			Lina	9	20	46	10
16	Gladisya	9	43	53	52			Zoey	8	68	56	63
Output

Is that even possible to match the row if they have same "Name" & "Grade"?

 

[Akuini]

The code works only if data is sorted by col Name and Grade (as your first example is).
So if it isn't the case then you need to sort it first then run the code.