VBA Macro to Find Missing and Duplicate Data

[QuillPet]

Hello,

I need a VBA code that will find the missing values in Sheet2 based on Sheet1, i.e., data in Sheet1 but not in sheet2. Afterward, I want to see the data in another sheet as given in the attached mini sheets. Also, I want to find out the duplicate values of Sheet1 and Sheet2. Can anyone create a VBA code to do so?

Much appreciation in advance!

Sheet1 Data:

Compare between 2 Sheets.xlsm
	A	B	C
1		BSA Agent X 20" Blue Pink	4
2		BSA Champ 16" Green	2
3		BSA Champ 16" Red	4
4		BSA Champ 16" Victor Blue	2
5		BSA Champ 20" Red	2
6		BSA Champ 20" Sea. Foam Green	8
7		BSA Champ 20" V.Blue	6
8		BSA Champ Orbit 20" Cel. Blue	12
9		BSA Champ Orbit 20" Red	6
10		BSA Daisy 14" Sky Blue	2
11		BSA Ladybird Diana 24" Doodle Pink	8
12		BSA Doodle 20" Victor Blue	4
13		BSA Go 20" Matt. Black Neon Orange	2
14		BSA Go Biking 14" Victor Blue Grey	2
15		BSA Go Biking 16" Ferrari Red & Led Grey	2
16		BSA Ladybird Hazel 24" Sports Purple	10
17		BSA Ladybird Hazel 24" Pearl Pink	12
18		BSA Kinder 14" Blue	2
19		BSA Kinder 14" Red	2
20		BSA Kinder 16" Red	2
21		BSA Kinder 16" Sunflower Yellow	6
22		BSA Kinder 16" Victor Blue	4
23		BSA Kinder 20" Doodle Pink	4
24		BSA Kinder 20" Lawn Green	2
25		BSA Kinder 20" S.Red	2
26		BSA Kinder 20" Sunflower Yellow	6
27		BSA Kinder 20" Victor Blue	6
28		BSA Ladybird Diana 26" Doodle Pink	22
29		BSA Ladybird Diana 26" Sea Foam Green	4
30		BSA Ladybird Diva 26" Clestial Blue	6
31		BSA Ladybird Diva 26" Sports Purple	2
32		BSA Ladybird Hazel 26" Artic .Blue	2
33		BSA Ladybird Hazel 26" Pearl Pin k	4
34		BSA Ladybird Hazel 26" Sports Purple	6
35		BSA Ladybird Shine 26" Citron Green	8
36		BSA Ladybird Shine 26" Clestial Blue	12
37		BSA Ladybird Shine 26" Pearl Pink	2
38		BSA Ladybird Sofia 26" Aqua Marine Blue M.Pink	6
39		BSA Ladybird Sofia 26" Larosa Pink Macron Pink	2
40		BSA Ladybird Summer 26" Pink	4
41		BSA Roamer 12" Red	2
42		BSA Roamer 16" Citron Green	2
43		BSA Roamer 16" Metalic Blue	2
44		BSA Roamer 16" Metalic Red	4
45		BSA Roamer 20" Green	4
46		BSA Roamer 20" Red	16
47		BSA Shine 24" Pink	20
48		BSA Toonz 16" Pink	2
49		BSA Trooper 20" Green Golden Yellow	2
50		Cosmic Comet 26" Black Orange	1
51		Cosmic Comet 26" Blue Yellow	1
52		Cosmic Comet 29" Black Blue	1
53		Cosmic Fern 24" Pink	1
54		Cosmic Jade 16" Pink	1
55		Cosmic Jade 16" Violet	2
56		Cosmic Jolt 29" Yellow Blue	1
57		Cosmic Level 9160 27.5 Silver Red	1
58		Hercules Streetcat Pro 24" Matt. Black Red	6
59		Hercules Wildrock IC RF 26" Black Red	2
60		Hercules Photon Speed Star 26" Matt. Black	2
61		Hercules A-220 HT DX2 29" SS Matt. Balck Twitter Blue	4
62		Hercules A-220 HT DX2 27.5" Matt. Black Frame Orange	3
63		Hercules Atom ZX - 26" Black Orange	2
64		Hercules Brut 2.0 ZX - 26" Matt. Black A. Green	2
65		Hercules Captain EX - Gents RB	2
66		Hercules Captain EX - Ladies RB	2
67		Hercules FX - 200 IC 26" Victor Blue Matt. Black	2
68		Hercules Kombat R/F 24" Black	4
69		Hercules Kombat RF - 26" Black	4
70		Hercules Kombat RF - 26" Red	4
71		Hercules Kombat ZX - 26" Matt. Balack A.Green	4
72		Hercules Sparx 2.0 RF 24" Black	6
73		Hercules Sparx 2.0 RF 26" Victor Blue	4
74		Hercules Standard Green	16
75		Hercules Street Rider R/F 26" Black	2
76		Hercules Street Rider RF 24" Black	2
77		Hercules Street Rider RF 24" S.Red	6
78		Hercules Street Rider RF 26" Victor Blue	8
79		Hercules Streetcat IC HT DX2 26" Matt Black	6
80		Hercules Streetcat Pro 24" IC - Victor Blue	6
81		Hercules Streetcat Pro 26" Acid Green	2
82		Hercules Streetcat Pro 26" Tropper Grey	2
83		Hercules Streetcat Pro HT DX2 26" Black A.Green	4
84		Hercules Streetcat Pro IC 24" A.Green	2
85		Hercules Streetcat Pro R/F 24" (DB - Rim) Black Green	4
86		Hercules Streetrider R/F 24" Smart Red	4
87		Hercules Streetrider ZX 24" Smart Red Black	2
88		Hercules Streetrider ZX 26" Black A.Green	6
89		Hercules Trailfire HT 26" Ivory White	6
90		Hercules Trailfire HT 26" Matt. Black N.Green	4
91		Hercules Trailfire R/F DX2 26" Magic Blue F.Orange	8
92		Hercules Tussle 26" RF Black	4
93		Hercules Tussle 26" RF Red	2
94		Hercules Wayfinder IC HT DX2 26" Matt. Black	2
95		Hercules Wildrock IC HT 26" Glossy Black Twitter Blue	2
96		Hercules Wildrock IC R/F 26" Glossy Black	4
97		Hercules Wildrock R/F 24" Black Twitter Blue	4
98		Hercules Wildrock R/F 26" Glossy Black	2
99		Hero Jackoo 12" Red	8
100		Hero Blaze 16" Purple	6
101		Hero Blaze 16" Red	2
102		Hero Blaze 16" Yellow	2
103		Hero Bobcat 16" Black	2
104		Hero Bobcat 20" Red	2
105		Hero Boomer 16 Orange	4
106		Hero Boomer 16 Pink	4
107		Hero Boomer 16" Green	2
108		Hero Boomer 20" Orange	2
109		Hero Boomer 20" Pink	2
110		Hero Colt 26" Black	2
111		Hero Colt 26" Sky Blue	2
112		Hero Jackoo 14	20
113		Hero Jet Green Standard	4
114		Hero Miss India Velvet 26" Pink	2
115		Hero Rafale 26" Black	4
116		Hero Ride On 16" Green	2
117		Hero Ride On 16" Red	4
118		Hero Ride On 20" Green	2
119		Hero Ride On 20" Red	4
120		Hero Rock Star 16" Pink	2
121		Hero Rock Star 20" Red	2
122		Hero Rockstar 12" Green	2
123		Hero Rockstar 12" Pink	4
124		Hero Rockstar 12" Red	2
125		Hero Sting 16" IC Black	4
126		Hero Sting IC 20" Red	2
127		Hero Sundance 20" Red	2
128		Hero Sundancer 16" Pink	4
129		Hero Sundancer 20" Pink	6
130		Hero Urban Trail 26" SS Black	2
131		Hero Urban Trail 26" SS Blue	2
132		Hero Urban Trail 26" SS Red	4
133		Hero Whistle 16" Black	2
134		Hero Whistle 16" Red	2
135		Hero Whistle 16" Sky Blue	2
136		Hero Whistle 20" Black	6
137		Hero Whistle 20" Sky Blue	2
138		Hero Winner 16" Black	2
139		Hero Winner 16" Red	2
140		Hercules FX - 100 DX2 3.0 26" SS Matt Black N.Orange	7
141		Machcity Munich 700 x 35 C SS Med. Pac. Blue	5
142		Machcity 26" S/S Med Forest Green	17
143		Machcity Munich 700 X 35 C 21 SP Med. Pac. Blue	1
144		Machcity S/S 26" Medium Victor Blue	25
145		Montra Downtown 700 C Med. Neon Yellow Black	2
146		Montra DTR 20" 6 SP Lava Red with Black	2
147		Montra DTR 20" 6 SP Red with Black Graphics	1
148		Ninety One Bombay 26" M.S DD Black Red	2
149		Ninety One Bombay 26" M.S DD Green	2
150		Ninety One Bombay 26" M.S DD Grey	1
151		Ninety One Drago 29" S/S DD Black Grey	1
152		Ninety One Skull Rider 26" SS Dual Disk - Black Yellow	1
153		Ninety One Tokyo 26" M.S DD Black Red	1
154		Old Stock - Hero BMX Rotter 20"	2
155		Old Stock - Hero RX2 26"	9
156		Old Stock - BSA BAZooka 26"	10
157		Old Stock - BSA Reflex 26"	18
158		Old Stock - Hercules Atom 26" Green	6
159		Raleigh Green Gents Standard 22 Gents	6
160		Riyoko Elite 24"	4
161		Riyoko Elite 26"	6
162		Riyoko Nicky 24"	8
163		Roadeo A-75 26" 21 SP D/D Black Neon Red	1
164		Roadeo A-75 26" 21 SP D/D Lapis Blue Neon Yellow	4
165		Roadeo Fugitive 27.5 21 SP DD Antiq Blue	1
166		Roadeo Fugitive 29" 21 SP DD Matt Black	2
167		Roadeo NFS 27.5" 21 SP D/D Black Neon Red	1
168		Roadeo NFS 27.5" 21 SP D/D Black Neon Yellow	2
169		Roadeo NFS 29" 21 SP D/D Black Neon Green	2
170		Rockstar 14" Red	2
171		Space Varder 16" Orange Ivory White	4
172		Space Varder 16" S.Purple	4
173		Space Varder 20" Sports Purple Hot pink	4
174		Space Varder 20"Smart Red	8
175		Top Trak Bull 20" Green	2
176		Top Trak Bull 20" Orange	6
177		Top Trak Challenger 20	14
178		Top Trak Floris 24" Blue	2
179		Top Trak Floris 26" Blue	2
180		Top Trak Floris 26" Pink	4
181		Top Trak Floris 26" Purple	6
182		Top Trak Fly Lady 24" Pink	6
183		Top Trak Fly Lady 24" Purple	2
184		Top Trak Kelly 16"	10
185		Top Trak Kelly 20"	8
186		Top Trak Krooz 20" Blue	6
187		Top Trak Spike Pro 20" Green	4
188		Top Trak Spike Pro 20" Orange	2
189	7/27/2023	BSA Ladybird Hazel 24" Sports Purple	2
190	7/27/2023	Hercules Sparx 2.0 RF 24" Black	4
191	7/27/2023	Hercules Streetcat Pro R/F 24" (DB - Rim) Black Green	2
192	7/27/2023	Hercules Tussle 26" RF Red	2
193	7/27/2023	Hercules Trailfire R/F DX2 26" Magic Blue F.Orange	2
194	7/27/2023	Hercules Wildrock IC R/F 26" Glossy Black	2
195	7/27/2023	Hero Colt 26" Black Red	1
196	7/27/2023	Hero Colt 26" Black Blue	1
197	7/27/2023	Hercules Streetcat Pro IC DX2 24" Black A.Green	2
198	7/27/2023	Hercules Streetcat Pro HT DX2 26" Black A.Green	2
199	7/27/2023	BSA Ladybird Diana 26" Sea Foam Green	2
200	7/27/2023	Hercules Streetcat Pro HT DX2 26" Black A.Green	2
201	7/27/2023	Hercules Streetrider ZX 26" Black A.Green	2
202	7/27/2023	Cosmic Level Tigo 29" SS ST FS Red	1
203	7/27/2023	Cosmic Jolt 29" Yellow Blue	1
204	7/27/2023	Ninety One Drago 27.5" S/S DD Black Red	1
205	7/27/2023	Ninety One Skull Rider 26" SS Dual Disk - Black Red	1
206	7/27/2023	BSA Ladybird Hazel 26" Sports Purple	2
207	7/27/2023	Hercules Trailfire HT 26" Matt. Black N.Green	2
208	7/27/2023	Hercules Kombat RF - 26" Black	2
209	7/27/2023	Hercules Standard Black	2
210	7/27/2023	Hercules Streetcat Pro 24" Matt. Black Red	1
211	7/27/2023	Cosmic Comet 26" SS Silver Red	1
212	7/27/2023	Hercules Street Rider RF 26" Victor Blue	1
213	7/27/2023	Cosmic Maze 27.5" SS ST FS	1
214	7/27/2023	Cosmic Maze 29" SS ST FS	2
215	7/27/2023	BSA Ladybird Diva 26" Sports Purple	2
216	7/27/2023	BSA Ladybird Summer 26" Victor Blue	2
217	7/27/2023	Cosmic Comet 29" SS ST FS Grey	2
218	7/27/2023	Hercules FX - 100 DX2 3.0 26" SS Matt Black N.Orange	1
219	7/27/2023	Cosmic Hulk 29" SS ST FS Silver Red	1
220	7/27/2023	BSA Champ Orbit 20" Cel. Blue	3
221	7/27/2023	Top Trak Challenger 20	1
222	7/27/2023	Ninety One Tokyo 27.5" SS DD Grey Yellow	2
223	7/27/2023	BSA Agent X 20" Blue Pink	2
224	7/27/2023	BSA Go Biking 20"	2
225	7/27/2023	BSA Ladybird Hazel 26" Sports Purple	2
226	7/27/2023	Ninety One Alpha Serenity 26" Black / Pink	2
227	7/27/2023	Ninety One Tokyo 27.5" SS DD Black Red	2
228	8/2/2023	BSA Ladybird Hazel 26" Pearl Pin k	12
229	8/2/2023	BSA Ladybird Hazel 26" Black	20
230	8/2/2023	BSA Ladybird Splash 26" B.Pink Pearl White	4
231	8/2/2023	BSA Ladybird Sofia 26" Pearl Pink	8
232	8/2/2023	Montra Unplugged FX SS 27.5 G Neon Green	1
233	8/4/2023	BSA Ladybird Summer 26" Victor Blue	20
234		BSA Ladybird Shine 26" Pearl Pink	8
235		BSA Ladybird Hazel 26" Sports Purple	8
236		Hercules Sparx 2.0 RF 24" Black	6
237		Hercules Tussle 26" RF Red	8
238		Hercules Sparx 2.0 RF 26" Victor Blue	4
239		BSA Ladybird Diana 24" Sea foam Green	6
240		BSA Trooper 20" Green Golden Yellow	2
241		BSA Wildcat 20" Cyan Green	2
242		BSA Platoon FX-20 " Trooper Grey	2
243		BSA Ladybird Valerie 26" Scarlet Red	1
244		Hercules Streetcat Pro 26" Tropper Grey	8
245		BSA Kinder 14 Ruby Pink	2
246		BSA Kinder 12" Victor Blue	2
247		BSA Roamer 12" Citron Green	6
248		BSA Champ 14" Seafoam Green	4
249		BSA Champ 12" Seafoam Green	4
250		Hercules Wildrock IC RF 26" Black Red	2
251		Hercules Bazooka 2.0 IC 26" Black Blue Yellow	8
252		Hercules Wildrock 26" HT Black Red	8
253		BSA Go Biking 14" Red and Grey	4
254		BSA Ladybird Diva 26" Sports Purple	10
255		Hercules Wildrock IC RF 24" Black Red	2
256		BSA Ladybird Diva 26" Doodle Pink	10
257	8/10/2023	BSA Ladybird Summer 26" Pink	10
258		Hercules Street Rider ZX 26 " Black Neon Red	10
259		Hercules Dynamite ZX 24" Black Acid Green	4
260		Hercules Trailfire R/F 26" Ivory White	2
261		Hercules Kombat R/F 24" Black	6
262		Hercules Standard Green	12
263		BSA Ladybird Valencia 26" Ballet Pink	2
264		BSA Platoon SS 20" Trooper Grey with Blue	2
265		Hercules Streetcat Pro IC RF 26" Black	8
266		Hercules Streetcat Pro HT IC DX2 26" Black A.Green	8
267		Hercules Wildrock IC RF 26" Black Red	6
268		BSA Ladybird Cindy 26" Blue Berry Blue	2
269		Hercules Wildrock IC RF 24" Black Twitter Blue	2
270		Hercules Bazooka 2.0 RF 24" Black Rubine Red	2
271		Top Track Ride 20"	12
272		Hercules Standard Black	12
273		Hercules Sparx 2.0 R/F 26" Black	8
274		BSA Champ 14" Victor Blue	2
275		BSA Ladybird Cindy 26" Doodle Pink	2
Sheet1

Sheet2 Data:

Compare between 2 Sheets.xlsm
	A	B
1	BSA Agent X 20" Blue Pink	4
2	BSA Champ 16" Green	2
3	BSA Champ 16" Red	4
4	BSA Champ 16" Victor Blue	2
5	BSA Champ 20" Red	2
6	BSA Champ 20" Sea. Foam Green	8
7	BSA Champ 20" V.Blue	6
8	BSA Champ Orbit 20" Cel. Blue	12
9	BSA Champ Orbit 20" Red	6
10	BSA Daisy 14" Sky Blue	2
11	BSA Diana 24" Doodle Pink	8
12	BSA Doodle 20" Victor Blue	4
13	BSA Go 20" Matt. Black Neon Orange	2
14	BSA Go Biking 14" Victor Blue Grey	2
15	BSA Go Biking 16" Ferrari Red & Led Grey	2
16	BSA Hazel 24" Sports Purple	12
17	BSA Hazel 24" Pearl Pink	12
18	BSA Kinder 14" Blue	2
19	BSA Kinder 14" Red	2
20	BSA Kinder 16" Red	2
21	BSA Kinder 16" Sunflower Yellow	6
22	BSA Kinder 16" Victor Blue	4
23	BSA Kinder 20" Doodle Pink	4
24	BSA Kinder 20" Lawn Green	2
25	BSA Kinder 20" S.Red	2
26	BSA Kinder 20" Sunflower Yellow	6
27	BSA Kinder 20" Victor Blue	6
28	BSA Ladybird Diana 26" Doodle Pink	22
29	BSA Ladybird Diana 26" Sea Foam Green	4
30	BSA Ladybird Diva 26" Clestial Blue	6
31	BSA Ladybird Diva 26" Sports Purple	2
32	BSA Ladybird Hazel 26" Artic .Blue	2
33	BSA Ladybird Hazel 26" Pearl Pin k	4
34	BSA Ladybird Hazel 26" Sports Purple	6
35	BSA Ladybird Shine 26" Citron Green	8
36	BSA Ladybird Shine 26" Clestial Blue	12
37	BSA Ladybird Shine 26" Pearl Pink	2
38	BSA Ladybird Sofia 26" Aqua Marine Blue M.Pink	6
39	BSA Ladybird Sofia 26" Larosa Pink Macron Pink	2
40	BSA Ladybird Summer 26" Pink	4
41	BSA Roamer 12" Red	2
42	BSA Roamer 16" Citron Green	2
43	BSA Roamer 16" Metalic Blue	2
44	BSA Roamer 16" Metalic Red	4
45	BSA Roamer 20" Green	4
46	BSA Roamer 20" Red	16
47	BSA Shine 24" Pink	20
48	BSA Toonz 16" Pink	2
49	BSA Trooper 20" Green Golden Yellow	2
50	Cosmic Comet 26" Black Orange	1
51	Cosmic Comet 26" Blue Yellow	1
52	Cosmic Comet 29" Black Blue	1
53	Cosmic Fern 24" Pink	1
54	Cosmic Jade 16" Pink	1
55	Cosmic Jade 16" Violet	2
56	Cosmic Jolt 29" Yellow Blue	1
57	Cosmic Level 9160 27.5 Silver Red	1
58	Hecules Streetcat Pro 24" Matt. Black Red	6
59	Hecules Wildrock IC RF 26" Black Red	2
60	Hercule Photon Speed Star 26" Matt. Black	2
61	Hercules A-220 HT DX2 29" SS Matt. Balck Twitter Blue	4
62	Hercules A-220 HT DX2 27.5" Matt. Black Frame Orange	3
63	Hercules Atom ZX - 26" Black Orange	2
64	Hercules Brut 2.0 ZX - 26" Matt. Black A. Green	2
65	Hercules Captain EX - Gents RB	2
66	Hercules Captain EX - Ladies RB	2
67	Hercules FX - 200 IC 26" Victor Blue Matt. Black	2
68	Hercules Kombat R/F 24" Black	4
69	Hercules Kombat RF - 26" Black	4
70	Hercules Kombat RF - 26" Red	4
71	Hercules Kombat ZX - 26" Matt. Balack A.Green	4
72	Hercules Sparx 2.0 RF 24" Black	6
73	Hercules Sparx 2.0 RF 26" Victor Blue	4
74	Hercules Standard Green	16
75	Hercules Street Rider R/F 26" Black	2
76	Hercules Street Rider RF 24" Black	2
77	Hercules Street Rider RF 24" S.Red	6
78	Hercules Street Rider RF 26" Victor Blue	8
79	Hercules Streetcat IC HT DX2 26" Matt Black	6
80	Hercules Streetcat Pro 24" IC - Victor Blue	6
81	Hercules Streetcat Pro 26" Acid Green	2
82	Hercules Streetcat Pro 26" Tropper Grey	2
83	Hercules Streetcat Pro HT DX2 26" Black A.Green	4
84	Hercules Streetcat Pro IC 24" A.Green	2
85	Hercules Streetcat Pro R/F 24" (DB - Rim) Black Green	4
86	Hercules Streetrider R/F 24" Smart Red	4
87	Hercules Streetrider ZX 24" Smart Red Black	2
88	Hercules Streetrider ZX 26" Black A.Green	6
89	Hercules Trailfire HT 26" Ivory White	6
90	Hercules Trailfire HT 26" Matt. Black N.Green	4
91	Hercules Trailfire R/F DX2 26" Magic Blue F.Orange	8
92	Hercules Tussle 26" RF Black	4
93	Hercules Tussle 26" RF Red	2
94	Hercules Wayfinder IC HT DX2 26" Matt. Black	2
95	Hercules Wildrock IC HT 26" Glossy Black Twitter Blue	2
96	Hercules Wildrock IC R/F 26" Glossy Black	4
97	Hercules Wildrock R/F 24" Black Twitter Blue	4
98	Hercules Wildrock R/F 26" Glossy Black	2
99	Hero Jackoo 12" Red	8
100	Hero Blaze 16" Purple	6
101	Hero Blaze 16" Red	2
102	Hero Blaze 16" Yellow	2
103	Hero Bobcat 16" Black	2
104	Hero Bobcat 20" Red	2
105	Hero Boomer 16 Orange	4
106	Hero Boomer 16 Pink	4
107	Hero Boomer 16" Green	2
108	Hero Boomer 20" Orange	2
109	Hero Boomer 20" Pink	2
110	Hero Colt 26" Black	2
111	Hero Colt 26" Sky Blue	2
112	Hero Jackoo 14	20
113	Hero Jet Green Standard	4
114	Hero Miss India Velvet 26" Pink	2
115	Hero Rafale 26" Black	4
116	Hero Ride On 16" Green	2
117	Hero Ride On 16" Red	4
118	Hero Ride On 20" Green	2
119	Hero Ride On 20" Red	4
120	Hero Rock Star 16" Pink	2
121	Hero Rock Star 20" Red	2
122	Hero Rockstar 12" Green	2
123	Hero Rockstar 12" Pink	4
124	Hero Rockstar 12" Red	2
125	Hero Sting 16" IC Black	4
126	Hero Sting IC 20" Red	2
127	Hero Sundance 20" Red	2
128	Hero Sundancer 16" Pink	4
129	Hero Sundancer 20" Pink	6
130	Hero Urban Trail 26" SS Black	2
131	Hero Urban Trail 26" SS Blue	2
132	Hero Urban Trail 26" SS Red	4
133	Hero Whistle 16" Black	2
134	Hero Whistle 16" Red	2
135	Hero Whistle 16" Sky Blue	2
136	Hero Whistle 20" Black	6
137	Hero Whistle 20" Sky Blue	2
138	Hero Winner 16" Black	2
139	Hero Winner 16" Red	2
140	Herules FX - 100 DX2 3.0 26" SS Matt Black N.Orange	7
141	Machcit Munich 700 x 35 C SS Med. Pac. Blue	5
142	Machcity 26" S/S Med Forest Green	17
143	Machcity Munich 700 X 35 C 21 SP Med. Pac. Blue	1
144	Machcity S/S 26" Medium Victor Blue	25
145	Montra Downtown 700 C Med. Neon Yellow Black	2
146	Montra DTR 20" 6 SP Lava Red with Black	2
147	Montra DTR 20" 6 SP Red with Black Graphics	1
148	Ninety One Bombay 26" M.S DD Black Red	2
149	Ninety One Bombay 26" M.S DD Green	2
150	Ninety One Bombay 26" M.S DD Grey	1
151	Ninety One Drago 29" S/S DD Black Grey	1
152	Ninety One Skull Rider 26" SS Dual Disk - Black Yellow	1
153	Ninety One Tokyo 26" M.S DD Black Red	1
154	Old Stock - Hero BMX Rotter 20"	2
155	Old Stock - Hero RX2 26"	9
156	Old Stock - BSA BAZooka 26"	10
157	Old Stock - BSA Reflex 26"	18
158	Old Stock - Hercules Atom 26" Green	6
159	Raleigh Green Gents Standard 22 Gents	6
160	Riyoko Elite 24"	4
161	Riyoko Elite 26"	6
162	Riyoko Nicky 24"	8
163	Roadeo A-75 26" 21 SP D/D Black Neon Red	1
164	Roadeo A-75 26" 21 SP D/D Lapis Blue Neon Yellow	4
165	Roadeo Fugitive 27.5 21 SP DD Antiq Blue	1
166	Roadeo Fugitive 29" 21 SP DD Matt Black	2
167	Roadeo NFS 27.5" 21 SP D/D Black Neon Red	1
168	Roadeo NFS 27.5" 21 SP D/D Black Neon Yellow	2
169	Roadeo NFS 29" 21 SP D/D Black Neon Green	2
170	Rockstar 14" Red	2
171	Space Varder 16" Orange Ivory White	4
172	Space Varder 16" S.Purple	4
173	Space Varder 20" Sports Purple Hot pink	4
174	Space Varder 20"Smart Red	8
175	Top Trak Bull 20" Green	2
176	Top Trak Bull 20" Orange	6
177	Top Trak Challenger 20	14
178	Top Trak Floris 24" Blue	2
179	Top Trak Floris 26" Blue	2
180	Top Trak Floris 26" Pink	4
181	Top Trak Floris 26" Purple	6
182	Top Trak Fly Lady 24" Pink	6
183	Top Trak Fly Lady 24" Purple	2
184	Top Trak Kelly 16"	10
185	Top Trak Kelly 20"	8
186	Top Trak Krooz 20" Blue	6
187	Top Trak Spike Pro 20" Green	4
188	Top Trak Spike Pro 20" Orange	2
189	Top Track Ride 20"	12
Sheet2

Expected Output:

Compare between 2 Sheets.xlsm
	A	B	C	D	E	F	G	H	I
1	Missing Values in Sheet2			Duplicate Values in Sheet1				Duplicate Values2
2	Date	Item	Quantity	Date	Item	Quantity	Date	Item	Quantity
3		BSA Ladybird Diana 24" Doodle Pink			BSA Diana 24" Doodle Pink			BSA Agent X 20" Blue Pink
4		BSA Ladybird Hazel 24" Sports Purple			BSA Hazel 24" Sports Purple			BSA Champ 16" Green
5		BSA Ladybird Hazel 24" Pearl Pink			BSA Hazel 24" Pearl Pink			BSA Champ 16" Red
6		Hercules Streetcat Pro 24" Matt. Black Red			Hecules Streetcat Pro 24" Matt. Black Red			BSA Champ 16" Victor Blue
7		Hercules Wildrock IC RF 26" Black Red			Hecules Wildrock IC RF 26" Black Red			BSA Champ 20" Red
8		Hercules Photon Speed Star 26" Matt. Black			Hercule Photon Speed Star 26" Matt. Black			BSA Champ 20" Sea. Foam Green
9		Hercules FX - 100 DX2 3.0 26" SS Matt Black N.Orange			Herules FX - 100 DX2 3.0 26" SS Matt Black N.Orange			BSA Champ 20" V.Blue
10		Machcity Munich 700 x 35 C SS Med. Pac. Blue			Machcit Munich 700 x 35 C SS Med. Pac. Blue			BSA Champ Orbit 20" Cel. Blue
11		Hero Colt 26" Black Red						BSA Champ Orbit 20" Red
12		Hero Colt 26" Black Blue						BSA Daisy 14" Sky Blue
13		Hercules Streetcat Pro IC DX2 24" Black A.Green						BSA Doodle 20" Victor Blue
14		Cosmic Level Tigo 29" SS ST FS Red						BSA Go 20" Matt. Black Neon Orange
15		Ninety One Drago 27.5" S/S DD Black Red						BSA Go Biking 14" Victor Blue Grey
16		Ninety One Skull Rider 26" SS Dual Disk - Black Red						BSA Go Biking 16" Ferrari Red & Led Grey
17		Hercules Standard Black						BSA Kinder 14" Blue
18		Cosmic Comet 26" SS Silver Red						BSA Kinder 14" Red
19		Cosmic Maze 27.5" SS ST FS						BSA Kinder 16" Red
20		Cosmic Maze 29" SS ST FS						BSA Kinder 16" Sunflower Yellow
21		BSA Ladybird Summer 26" Victor Blue						BSA Kinder 16" Victor Blue
22		Cosmic Comet 29" SS ST FS Grey						BSA Kinder 20" Doodle Pink
23		Cosmic Hulk 29" SS ST FS Silver Red						BSA Kinder 20" Lawn Green
24		Ninety One Tokyo 27.5" SS DD Grey Yellow						BSA Kinder 20" S.Red
25		BSA Go Biking 20"						BSA Kinder 20" Sunflower Yellow
26		Ninety One Alpha Serenity 26" Black / Pink						BSA Kinder 20" Victor Blue
27		Ninety One Tokyo 27.5" SS DD Black Red						BSA Ladybird Diana 26" Doodle Pink
28		BSA Ladybird Hazel 26" Black						BSA Ladybird Diana 26" Sea Foam Green
29		BSA Ladybird Splash 26" B.Pink Pearl White						BSA Ladybird Diva 26" Clestial Blue
30		BSA Ladybird Sofia 26" Pearl Pink						BSA Ladybird Diva 26" Sports Purple
31		Montra Unplugged FX SS 27.5 G Neon Green						BSA Ladybird Hazel 26" Artic .Blue
32		BSA Ladybird Diana 24" Sea foam Green						BSA Ladybird Hazel 26" Pearl Pin k
33		BSA Wildcat 20" Cyan Green						BSA Ladybird Hazel 26" Sports Purple
34		BSA Platoon FX-20 " Trooper Grey						BSA Ladybird Shine 26" Citron Green
35		BSA Ladybird Valerie 26" Scarlet Red						BSA Ladybird Shine 26" Clestial Blue
36		BSA Kinder 14 Ruby Pink						BSA Ladybird Shine 26" Pearl Pink
37		BSA Kinder 12" Victor Blue						BSA Ladybird Sofia 26" Aqua Marine Blue M.Pink
38		BSA Roamer 12" Citron Green						BSA Ladybird Sofia 26" Larosa Pink Macron Pink
39		BSA Champ 14" Seafoam Green						BSA Ladybird Summer 26" Pink
40		BSA Champ 12" Seafoam Green						BSA Roamer 12" Red
41		Hercules Bazooka 2.0 IC 26" Black Blue Yellow						BSA Roamer 16" Citron Green
42		Hercules Wildrock 26" HT Black Red						BSA Roamer 16" Metalic Blue
43		BSA Go Biking 14" Red and Grey						BSA Roamer 16" Metalic Red
44		Hercules Wildrock IC RF 24" Black Red						BSA Roamer 20" Green
45		BSA Ladybird Diva 26" Doodle Pink						BSA Roamer 20" Red
46		Hercules Street Rider ZX 26 " Black Neon Red						BSA Shine 24" Pink
47		Hercules Dynamite ZX 24" Black Acid Green						BSA Toonz 16" Pink
48		Hercules Trailfire R/F 26" Ivory White						BSA Trooper 20" Green Golden Yellow
49		BSA Ladybird Valencia 26" Ballet Pink						Cosmic Comet 26" Black Orange
50		BSA Platoon SS 20" Trooper Grey with Blue						Cosmic Comet 26" Blue Yellow
51		Hercules Streetcat Pro IC RF 26" Black						Cosmic Comet 29" Black Blue
52		Hercules Streetcat Pro HT IC DX2 26" Black A.Green						Cosmic Fern 24" Pink
53		BSA Ladybird Cindy 26" Blue Berry Blue						Cosmic Jade 16" Pink
54		Hercules Wildrock IC RF 24" Black Twitter Blue						Cosmic Jade 16" Violet
55		Hercules Bazooka 2.0 RF 24" Black Rubine Red						Cosmic Jolt 29" Yellow Blue
56		Hercules Sparx 2.0 R/F 26" Black						Cosmic Level 9160 27.5 Silver Red
57		BSA Champ 14" Victor Blue						Hercules A-220 HT DX2 29" SS Matt. Balck Twitter Blue
58		BSA Ladybird Cindy 26" Doodle Pink						Hercules A-220 HT DX2 27.5" Matt. Black Frame Orange
59								Hercules Atom ZX - 26" Black Orange
60								Hercules Brut 2.0 ZX - 26" Matt. Black A. Green
61								Hercules Captain EX - Gents RB
62								Hercules Captain EX - Ladies RB
63								Hercules FX - 200 IC 26" Victor Blue Matt. Black
64								Hercules Kombat R/F 24" Black
65								Hercules Kombat RF - 26" Black
66								Hercules Kombat RF - 26" Red
67								Hercules Kombat ZX - 26" Matt. Balack A.Green
68								Hercules Sparx 2.0 RF 24" Black
69								Hercules Sparx 2.0 RF 26" Victor Blue
70								Hercules Standard Green
71								Hercules Street Rider R/F 26" Black
72								Hercules Street Rider RF 24" Black
73								Hercules Street Rider RF 24" S.Red
74								Hercules Street Rider RF 26" Victor Blue
75								Hercules Streetcat IC HT DX2 26" Matt Black
76								Hercules Streetcat Pro 24" IC - Victor Blue
77								Hercules Streetcat Pro 26" Acid Green
78								Hercules Streetcat Pro 26" Tropper Grey
79								Hercules Streetcat Pro HT DX2 26" Black A.Green
80								Hercules Streetcat Pro IC 24" A.Green
81								Hercules Streetcat Pro R/F 24" (DB - Rim) Black Green
82								Hercules Streetrider R/F 24" Smart Red
83								Hercules Streetrider ZX 24" Smart Red Black
84								Hercules Streetrider ZX 26" Black A.Green
85								Hercules Trailfire HT 26" Ivory White
86								Hercules Trailfire HT 26" Matt. Black N.Green
87								Hercules Trailfire R/F DX2 26" Magic Blue F.Orange
88								Hercules Tussle 26" RF Black
89								Hercules Tussle 26" RF Red
90								Hercules Wayfinder IC HT DX2 26" Matt. Black
91								Hercules Wildrock IC HT 26" Glossy Black Twitter Blue
92								Hercules Wildrock IC R/F 26" Glossy Black
93								Hercules Wildrock R/F 24" Black Twitter Blue
94								Hercules Wildrock R/F 26" Glossy Black
95								Hero Jackoo 12" Red
96								Hero Blaze 16" Purple
97								Hero Blaze 16" Red
98								Hero Blaze 16" Yellow
99								Hero Bobcat 16" Black
100								Hero Bobcat 20" Red
101								Hero Boomer 16 Orange
102								Hero Boomer 16 Pink
103								Hero Boomer 16" Green
104								Hero Boomer 20" Orange
105								Hero Boomer 20" Pink
106								Hero Colt 26" Black
107								Hero Colt 26" Sky Blue
108								Hero Jackoo 14
109								Hero Jet Green Standard
110								Hero Miss India Velvet 26" Pink
111								Hero Rafale 26" Black
112								Hero Ride On 16" Green
113								Hero Ride On 16" Red
114								Hero Ride On 20" Green
115								Hero Ride On 20" Red
116								Hero Rock Star 16" Pink
117								Hero Rock Star 20" Red
118								Hero Rockstar 12" Green
119								Hero Rockstar 12" Pink
120								Hero Rockstar 12" Red
121								Hero Sting 16" IC Black
122								Hero Sting IC 20" Red
123								Hero Sundance 20" Red
124								Hero Sundancer 16" Pink
125								Hero Sundancer 20" Pink
126								Hero Urban Trail 26" SS Black
127								Hero Urban Trail 26" SS Blue
128								Hero Urban Trail 26" SS Red
129								Hero Whistle 16" Black
130								Hero Whistle 16" Red
131								Hero Whistle 16" Sky Blue
132								Hero Whistle 20" Black
133								Hero Whistle 20" Sky Blue
134								Hero Winner 16" Black
135								Hero Winner 16" Red
136								Machcity 26" S/S Med Forest Green
137								Machcity Munich 700 X 35 C 21 SP Med. Pac. Blue
138								Machcity S/S 26" Medium Victor Blue
139								Montra Downtown 700 C Med. Neon Yellow Black
140								Montra DTR 20" 6 SP Lava Red with Black
141								Montra DTR 20" 6 SP Red with Black Graphics
142								Ninety One Bombay 26" M.S DD Black Red
143								Ninety One Bombay 26" M.S DD Green
144								Ninety One Bombay 26" M.S DD Grey
145								Ninety One Drago 29" S/S DD Black Grey
146								Ninety One Skull Rider 26" SS Dual Disk - Black Yellow
147								Ninety One Tokyo 26" M.S DD Black Red
148								Old Stock - Hero BMX Rotter 20"
149								Old Stock - Hero RX2 26"
150								Old Stock - BSA BAZooka 26"
151								Old Stock - BSA Reflex 26"
152								Old Stock - Hercules Atom 26" Green
153								Raleigh Green Gents Standard 22 Gents
154								Riyoko Elite 24"
155								Riyoko Elite 26"
156								Riyoko Nicky 24"
157								Roadeo A-75 26" 21 SP D/D Black Neon Red
158								Roadeo A-75 26" 21 SP D/D Lapis Blue Neon Yellow
159								Roadeo Fugitive 27.5 21 SP DD Antiq Blue
160								Roadeo Fugitive 29" 21 SP DD Matt Black
161								Roadeo NFS 27.5" 21 SP D/D Black Neon Red
162								Roadeo NFS 27.5" 21 SP D/D Black Neon Yellow
163								Roadeo NFS 29" 21 SP D/D Black Neon Green
164								Rockstar 14" Red
165								Space Varder 16" Orange Ivory White
166								Space Varder 16" S.Purple
167								Space Varder 20" Sports Purple Hot pink
168								Space Varder 20"Smart Red
169								Top Trak Bull 20" Green
170								Top Trak Bull 20" Orange
171								Top Trak Challenger 20
172								Top Trak Floris 24" Blue
173								Top Trak Floris 26" Blue
174								Top Trak Floris 26" Pink
175								Top Trak Floris 26" Purple
176								Top Trak Fly Lady 24" Pink
177								Top Trak Fly Lady 24" Purple
178								Top Trak Kelly 16"
179								Top Trak Kelly 20"
180								Top Trak Krooz 20" Blue
181								Top Trak Spike Pro 20" Green
182								Top Trak Spike Pro 20" Orange
183								Top Track Ride 20"
Expected Result

 

[kevin9999]

There's a number of ways you could do this (e.g. scripting dictionaries) but seeing as you have 365 - why not take advantage of the available functions? Here's a method that puts formulas on the Expected Results sheet, then converts to values. Change the sheet names to suit, and please try it on a copy of your workbook first. The result I get varies from what you posted above, but I'm a little unclear as to what you mean by duplicate values - I'm assuming you meant list values from each of sheets 1 & 2 that were duplicates on their respective sheets? If that's the case, then the result I got is correct when I checked the respective sheets for duplicates.

Option Explicit
Sub Compare_With_Formulas()
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
    Set ws1 = Worksheets("Sheet1")          '<-- *** Change sheet names to suit ***
    Set ws2 = Worksheets("Sheet2")
    Set ws3 = Worksheets("Expected Result")
    
    With ws3
        .Range("B3", ws3.Cells(Rows.Count, "B").End(xlUp)).ClearContents
        .Range("E3", ws3.Cells(Rows.Count, "E").End(xlUp)).ClearContents
        .Range("H3", ws3.Cells(Rows.Count, "H").End(xlUp)).ClearContents
    End With
    
    Dim LRow1 As Long, LRow2 As Long
    LRow1 = ws1.Cells(Rows.Count, "B").End(xlUp).Row
    LRow2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row
    
    ws3.Range("B3").Formula2 = "=IFERROR(FILTER(Sheet2!A1:A" & LRow2 & ",COUNTIF(Sheet1!B2:B" & LRow1 & ",Sheet2!A1:A" & LRow2 & ")=0),""no missing values found"")"
    ws3.Range("E3").Formula2 = "=IFERROR(UNIQUE(FILTER(Sheet1!B2:B" & LRow1 & ",COUNTIF(Sheet1!B2:B" & LRow1 & ",Sheet1!B2:B" & LRow1 & ")>1)),""no duplicates found"")"
    ws3.Range("H3").Formula2 = "=IFERROR(UNIQUE(FILTER(Sheet2!A1:A" & LRow2 & ",COUNTIF(Sheet2!A1:A" & LRow2 & ",Sheet2!A1:A" & LRow2 & ")>1)),""no duplicates found"")"
    With ws3.Range("B3#")
        .Value2 = .Value2
    End With
    With ws3.Range("E3#")
        .Value2 = .Value2
    End With
    With ws3.Range("H3#")
        .Value2 = .Value2
    End With
End Sub

 

[QuillPet]

There's a number of ways you could do this (e.g. scripting dictionaries) but seeing as you have 365 - why not take advantage of the available functions? Here's a method that puts formulas on the Expected Results sheet, then converts to values. Change the sheet names to suit, and please try it on a copy of your workbook first. The result I get varies from what you posted above, but I'm a little unclear as to what you mean by duplicate values - I'm assuming you meant list values from each of sheets 1 & 2 that were duplicates on their respective sheets? If that's the case, then the result I got is correct when I checked the respective sheets for duplicates.

Hello Kevin,

That is an interesting way to do this. And, it works. Thank you very much!

However, I need a code that will work in other versions too in this case. I see you have used the FILTER function that is used only in Microsoft 365. Can you provide another code to do so?

 

[kevin9999]

Yes. I've switched my laptop off for tonight, so tomorrow if that's OK?

 

[QuillPet]

Yes. I've switched my laptop off for tonight, so tomorrow if that's OK?

Sure. I will wait. Thanks for your efforts, Kevin.

 

[kevin9999]

Please try the following on a copy of your workbook. Should work with versions earlier than 365.

Option Explicit
Sub Compare_2_Lists()
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
    Set ws1 = Worksheets("Sheet1")              '<-- *** Change sheet names to suit ***
    Set ws2 = Worksheets("Sheet2")
    Set ws3 = Worksheets("Expected Result")
    
    'Clear any existing results
    With ws3
        .Range("B3:B" & Application.Max(ws3.Cells(.Rows.Count, "B").End(xlUp).Row, 3)).ClearContents
        .Range("E3:E" & Application.Max(ws3.Cells(.Rows.Count, "E").End(xlUp).Row, 3)).ClearContents
        .Range("H3:H" & Application.Max(ws3.Cells(.Rows.Count, "H").End(xlUp).Row, 3)).ClearContents
    End With
    
    'Get the data missing on sheet 2
    Dim a, b, c
    Dim i As Long, j As Long
    a = ws1.Range("B1", ws1.Cells(Rows.Count, "B").End(xlUp))
    b = ws2.Range("A1", ws2.Cells(Rows.Count, "A").End(xlUp))
    ReDim c(1 To UBound(a, 1), 1 To 1)
    With CreateObject("scripting.dictionary")
        .comparemode = 1
        For i = 1 To UBound(a, 1)
            .Item(a(i, 1)) = Empty
        Next i
        For i = 1 To UBound(b, 1)
            If Not .exists(b(i, 1)) Then
                j = j + 1
                c(j, 1) = b(i, 1)
            End If
        Next i
    End With
    ws3.Range("B3").Resize(j).Value = c
    
    'Get the duplicates on sheet 1
    Dim LRow As Long, rCrit As Range, rCopyTo As Range
    LRow = ws1.Cells(Rows.Count, "B").End(xlUp).Row
    Set rCopyTo = ws3.Range("E3")
    With ws1.Range("B1:B" & LRow)
        Set rCrit = .Offset(, .Columns.Count + 1).Resize(2, 1)
        rCrit.Cells(2, 1).Formula = "=COUNTIF(B2:B" & LRow & ",B2)>1"
        .AdvancedFilter xlFilterCopy, rCrit, rCopyTo, 1
        ws1.Rows(1).Delete
    End With
    rCrit.Cells(1).Resize(2, 1).ClearContents
    ws3.Range("E3").Delete xlUp
    
    'Get the duplicates on sheet 2
    LRow = ws2.Cells(Rows.Count, "A").End(xlUp).Row
    Set rCopyTo = ws3.Range("H3")
    With ws2.Range("A1:A" & LRow)
        Set rCrit = .Offset(, .Columns.Count + 1).Resize(2, 1)
        rCrit.Cells(2, 1).Formula = "=COUNTIF(A2:A" & LRow & ",A2)>1"
        .AdvancedFilter xlFilterCopy, rCrit, rCopyTo, 1
        ws2.Rows(1).Delete
    End With
    rCrit.Cells(1).Resize(2, 1).ClearContents
    ws3.Range("H3").Delete xlUp

End Sub

 

[QuillPet]

Please try the following on a copy of your workbook. Should work with versions earlier than 365

Thank you, Kevin. This is a lifesaver. Cheers!

 

[kevin9999]

Happy to help & welcome to the forum

 

[QuillPet]

Please try the following on a copy of your workbook. Should work with versions earlier than 365.

Hello @kevin9999 ,

I need your attention one more time.
As per the report, we mentioned the Date, Item, and Quantity for each report,
ie Missing Values in Sheet2, Duplicate Value in Sheet1, and Duplicate values in Sheet2.
Can we show the Date and Item for each report in the report sheet if there is a date and quantity in the source sheets? Also, it will be good if you can show the row number of source for each item.

 

[kevin9999]

Can we show the Date and Item for each report in the report sheet if there is a date and quantity in the source sheets?

Do you want to list every occurence of the duplicated item from the source sheet? The reason I ask is that, using Hercules Sparx 2.0 RF 24" Black as an example, there are 3 instances of that item on sheet 1 - only the second one has a date, the first and third instance have quantities of 6, the second has a quantity of 4. Which instance would you want, or all three?