Compare 2 lists from 2 different worksheets

[Flavien]

Hello!

I would like to compare 2 lists that contain file numbers all named P1234-A or P12345-A (one letter, 4 or 5 digits, one letter). I would like to compare 2 lists that contain file numbers all named P1234-A or P12345-A (one letter, 4 or 5 digits, one letter). The first list is exhaustive, it contains all the file numbers Up to date.

In the second list many file numbers are missing, or are too old. Only the final letter can change.

I would like to make a macro that would allow me to compare these 2 lists by telling me if the file numbers match or not or if the number does not exist.

I have not found anything on the site that I know how to exploit. Do you have an idea? Can you help me?

Thank you fort time and your help.

 

[alansidman]

Please provide with XL2BB (no pictures) of your sample worksheet and a mocked up solution.

 

[Flavien]

Hello,
I would like to compare the lists in D&E columns. D is my exhaustive list (well normally) if however a data from column E does not exist in column D, I would like the transferred data to be highlighted in yellow. In addition, the macro does not remove duplicates.

Do you have idea to delete duplicates and highlight added datas?

Thanks

Classeur1
	A	B	C	D	E	F
1				liste ERP	Lise GCU
2				1001 - A	1002 - B
3				1002 - B	1003 - C
4				1003 - C	1004 - D
5				1004 - D	1006 - A
6				1005 - E	1007 - B
7				1006 - A	1008 - C
8				1007 - B	1009 - A
9				1008 - C	1010 - B
10				1009 - A	1011 - A
11				1010 - B	1012 - B
12				1011 - A	1014 - D
13				1012 - B	1015 - E
14				1013 - C	1016 - A
15					1011-A
16					1011-B
17					1014-A
18					1002 - B
19					1003 - C
20					1004 - D
21					1006 - A
22					1007 - B
23					1008 - C
24					1009 - A
25					1010 - B
26					1011 - A
27					1012 - B
28					1014 - D
29					1015 - E
30					1016 - A
31					1011-A
32					1011-B
33					1014-A
34					1002 - B
35					1003 - C
36					1004 - D
37					1006 - A
38					1007 - B
39					1008 - C
40					1009 - A
41					1010 - B
42					1011 - A
43					1012 - B
44					1014 - D
45					1015 - E
46					1016 - A
47					1011-A
48					1011-B
49					1014-A
50
Feuil1

Classeur1
	A	B
1	liste ERP	Lise GCU
2	1001 - A
3	1002 - B	1002 - B
4	1002 - B	1002 - B
5	1002 - B	1002 - B
6	1003 - C	1003 - C
7	1003 - C	1003 - C
8	1003 - C	1003 - C
9	1004 - D	1004 - D
10	1004 - D	1004 - D
11	1004 - D	1004 - D
12	1005 - E
13	1006 - A	1006 - A
14	1006 - A	1006 - A
15	1006 - A	1006 - A
16	1007 - B	1007 - B
17	1007 - B	1007 - B
18	1007 - B	1007 - B
19	1008 - C	1008 - C
20	1008 - C	1008 - C
21	1008 - C	1008 - C
22	1009 - A	1009 - A
23	1009 - A	1009 - A
24	1009 - A	1009 - A
25	1010 - B	1010 - B
26	1010 - B	1010 - B
27	1010 - B	1010 - B
28	1011 - A	1011 - A
29	1011 - A	1011 - A
30	1011 - A	1011 - A
31	1011-A	1011-A
32	1011-A	1011-A
33	1011-A	1011-A
34	1011-B	1011-B
35	1011-B	1011-B
36	1011-B	1011-B
37	1012 - B	1012 - B
38	1012 - B	1012 - B
39	1012 - B	1012 - B
40	1013 - C
41	1014 - D	1014 - D
42	1014 - D	1014 - D
43	1014 - D	1014 - D
44	1014-A	1014-A
45	1014-A	1014-A
46	1014-A	1014-A
47	1015 - E	1015 - E
48	1015 - E	1015 - E
49	1015 - E	1015 - E
50	1016 - A	1016 - A
51	1016 - A	1016 - A
52	1016 - A	1016 - A
53
Feuil16

 

[Flavien]

Below the macro used:

Sub comparerlistes()
Dim MasterListRange As Range
Dim WeeklyListRange As Range
Dim vMaster As Variant
Dim vWeek As Variant
Dim MasterListRows As Long
Dim WeeklyListRows As Long
Dim vR() As Variant
Dim i As Long, n As Long, j As Long
Dim isExist As Boolean
Dim Ws As Worksheet

MasterListRows = Sheets("Feuil1").Cells(Rows.Count, 4).End(xlUp).Row '<~~ Correct column number
WeeklyListRows = Sheets("Feuil1").Cells(Rows.Count, 5).End(xlUp).Row '<~~ Correct column number

Set MasterListRange = Sheets("Feuil1").Range("D2:D" & MasterListRows)
Set WeeklyListRange = Sheets("Feuil1").Range("E2:E" & WeeklyListRows)

vMaster = MasterListRange
vWeek = WeeklyListRange

For i = 1 To UBound(vWeek, 1)
If WorksheetFunction.CountIf(MasterListRange, UCase(vWeek(i, 1))) Then
n = n + 1
ReDim Preserve vR(1 To 2, 1 To n)
vR(1, n) = UCase(vWeek(i, 1))
vR(2, n) = vWeek(i, 1)
Else
n = n + 1
ReDim Preserve vR(1 To 2, 1 To n)
vR(1, n) = UCase(vWeek(i, 1))
vR(2, n) = vWeek(i, 1)
End If
Next i
For j = 1 To UBound(vMaster, 1)
isExist = False
For i = 1 To UBound(vWeek, 1)
If vMaster(j, 1) = UCase(vWeek(i, 1)) Then
isExist = True
Exit For
End If
Next i
If Not isExist Then
n = n + 1
ReDim Preserve vR(1 To 2, 1 To n)
vR(1, n) = vMaster(j, 1)
End If
Next j
Set Ws = Sheets.Add '<~~ Sheets("Your seetname")
With Ws
.Range("a1").Resize(1, 2) = Sheets("Feuil1").Range("d1").Resize(1, 2).Value
.Range("a2").Resize(n, 2) = WorksheetFunction.Transpose(vR)
.Range("a1").CurrentRegion.Sort .Range("a1"), xlAscending, Header:=xlYes
End With
End Sub