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

 

[QuillPet]

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?

Hello Kevin,

For multiple occurrences like that, I want to get all three matches if possible.

 

[kevin9999]

Also, it will be good if you can show the row number of source for each item.

How exactly do you want to show that? In another column on the expected results sheet?

 

[QuillPet]

How exactly do you want to show that? In another column on the expected results sheet?

Yes, I want them in a new column on the expected results sheet for each item.

 

[kevin9999]

Like this?

QuillPet.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1		Missing values in sheet 2				Duplicate values in sheet 1				Duplicate values in sheet 2
2	Source Row	Item	Quantity	Source Row	Date	Item	Quantity	Source Row	Date	Item	Quantity
Expected Result

 

[QuillPet]

Like this?

QuillPet.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1		Missing values in sheet 2				Duplicate values in sheet 1				Duplicate values in sheet 2
2	Source Row	Item	Quantity	Source Row	Date	Item	Quantity	Source Row	Date	Item	Quantity
Expected Result

yes, it would be much better. Also, please do not forget to show the Date and Item for each report in the report sheet if there is a date and quantity in the source sheets.

 

[kevin9999]

I thought it more logical to change the column order to this. Please try it on a copy of your workbook. Could do with tidying up, but try it first.

Option Explicit
Sub Compare_2_Lists_V2()
    Application.ScreenUpdating = False
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, LRow As Long
    Set ws1 = Worksheets("Sheet1")              '<-- *** Change sheet names to suit ***
    Set ws2 = Worksheets("Sheet2")
    Set ws3 = Worksheets("Expected Result")
    If ws1.AutoFilterMode Then ws1.AutoFilter.ShowAllData
    
    'Clear any existing results
    LRow = Application.Max(ws3.Cells.Find("*", , xlFormulas, , 1, 2).Row, 3)
    ws3.Range("A3:Z" & LRow).ClearContents
    
    'Get the data missing on sheet 2
    Dim a, b, c, d, x
    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("A3").Resize(j).Value = c
    
    'Get the duplicates on sheet 1
    With ws1
        .Activate
        .Rows(1).Insert
        .Range("A1:E1").Value = Array("Date", "Item", "Qty", "Row", "Count")
    End With
    LRow = ws1.Cells(Rows.Count, "B").End(xlUp).Row
    With ws1.Range("D2:D" & LRow)
        .Value = Evaluate("Row(" & .Address(, , , 1) & ")")
    End With
    With ws1.Range("E2:E" & LRow)
        .Value = Evaluate("Countif(B2:B" & LRow & "," & .Offset(, -3).Address(, , , 1) & ")")
    End With
    
    With ws1.Range("A1").CurrentRegion
        .AutoFilter 5, ">1"
        If .SpecialCells(xlCellTypeVisible).Address <> .Rows(1).Address Then
            .Offset(1).Resize(, 4).Copy ws3.Range("D3")
        End If
        .AutoFilter
        .Range("D:E").ClearContents
        .Rows(1).Delete
    End With
    
    'Get the duplicates on sheet 2 (if any) and the qty/row on the missing from sheet 2
    With ws2
        .Activate
        .Rows(1).Insert
        .Range("A1:D1").Value = Array("Item", "Qty", "Row", "Count")
    End With
    LRow = ws2.Cells(Rows.Count, "A").End(xlUp).Row
    With ws2.Range("C2:C" & LRow)
        .Value = Evaluate("Row(" & .Address(, , , 1) & ")")
    End With
    With ws2.Range("D2:D" & LRow)
        .Value = Evaluate("Countif(A2:A" & LRow & "," & .Offset(, -3).Address(, , , 1) & ")")
    End With
    With ws2.Range("A1").CurrentRegion
        .AutoFilter 4, ">1"
        If .SpecialCells(xlCellTypeVisible).Address <> .Rows(1).Address Then
            .Offset(1).Resize(, 3).Copy ws3.Range("H3")
        End If
        .AutoFilter
        LRow = Application.Max(ws2.Cells(Rows.Count, "A").End(xlUp).Row, 2)
        d = ws2.Range("A2:D" & LRow)
        x = ws3.Range("A3:C" & ws3.Cells(Rows.Count, "A").End(xlUp).Row)
        For i = 1 To UBound(x, 1)
            For j = LBound(d, 1) To UBound(d, 1)
                If d(j, 1) = x(i, 1) Then x(i, 2) = d(j, 2): x(i, 3) = d(j, 3)
            Next j
        Next i
        ws3.Range("A3").Resize(UBound(x, 1), 3).Value = x
        .Range("C:D").ClearContents
        .Rows(1).Delete
    End With
    Application.Goto ws3.Cells(1), 1
    Application.ScreenUpdating = True
End Sub

Results in this:

QuillPet.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Missing values in sheet 2				Duplicate values in sheet 1			Duplicate values in sheet 2
2	Item	Quantity	Source Row	Date	Item	Quantity	Source Row	Item	Quantity	Source Row
3	BSA Diana 24" Doodle Pink	8	12		BSA Agent X 20" Blue Pink	4	2
4	BSA Hazel 24" Sports Purple	12	17		BSA Champ Orbit 20" Cel. Blue	12	9
5	BSA Hazel 24" Pearl Pink	12	18		BSA Ladybird Hazel 24" Sports Purple	10	17
6	Hecules Streetcat Pro 24" Matt. Black Red	6	59		BSA Ladybird Diana 26" Sea Foam Green	4	30
7	Hecules Wildrock IC RF 26" Black Red	2	60		BSA Ladybird Diva 26" Sports Purple	2	32
8	Hercule Photon Speed Star 26" Matt. Black	2	61		BSA Ladybird Hazel 26" Pearl Pin k	4	34
9	Herules FX - 100 DX2 3.0 26" SS Matt Black N.Orange	7	141		BSA Ladybird Hazel 26" Sports Purple	6	35
10	Machcit Munich 700 x 35 C SS Med. Pac. Blue	5	142		BSA Ladybird Shine 26" Pearl Pink	2	38
11					BSA Ladybird Summer 26" Pink	4	41
12					BSA Trooper 20" Green Golden Yellow	2	50
13					Cosmic Jolt 29" Yellow Blue	1	57
14					Hercules Streetcat Pro 24" Matt. Black Red	6	59
15					Hercules Wildrock IC RF 26" Black Red	2	60
16					Hercules Kombat R/F 24" Black	4	69
17					Hercules Kombat RF - 26" Black	4	70
18					Hercules Sparx 2.0 RF 24" Black	6	73
19					Hercules Sparx 2.0 RF 26" Victor Blue	4	74
20					Hercules Standard Green	16	75
21					Hercules Street Rider RF 26" Victor Blue	8	79
22					Hercules Streetcat Pro 26" Tropper Grey	2	83
23					Hercules Streetcat Pro HT DX2 26" Black A.Green	4	84
24					Hercules Streetcat Pro R/F 24" (DB - Rim) Black Green	4	86
25					Hercules Streetrider ZX 26" Black A.Green	6	89
26					Hercules Trailfire HT 26" Matt. Black N.Green	4	91
27					Hercules Trailfire R/F DX2 26" Magic Blue F.Orange	8	92
28					Hercules Tussle 26" RF Red	2	94
29					Hercules Wildrock IC R/F 26" Glossy Black	4	97
30					Hercules FX - 100 DX2 3.0 26" SS Matt Black N.Orange	7	141
31					Top Trak Challenger 20	14	178
32				7/27/2023	BSA Ladybird Hazel 24" Sports Purple	2	190
33				7/27/2023	Hercules Sparx 2.0 RF 24" Black	4	191
34				7/27/2023	Hercules Streetcat Pro R/F 24" (DB - Rim) Black Green	2	192
35				7/27/2023	Hercules Tussle 26" RF Red	2	193
36				7/27/2023	Hercules Trailfire R/F DX2 26" Magic Blue F.Orange	2	194
37				7/27/2023	Hercules Wildrock IC R/F 26" Glossy Black	2	195
38				7/27/2023	Hercules Streetcat Pro HT DX2 26" Black A.Green	2	199
39				7/27/2023	BSA Ladybird Diana 26" Sea Foam Green	2	200
40				7/27/2023	Hercules Streetcat Pro HT DX2 26" Black A.Green	2	201
41				7/27/2023	Hercules Streetrider ZX 26" Black A.Green	2	202
42				7/27/2023	Cosmic Jolt 29" Yellow Blue	1	204
43				7/27/2023	BSA Ladybird Hazel 26" Sports Purple	2	207
44				7/27/2023	Hercules Trailfire HT 26" Matt. Black N.Green	2	208
45				7/27/2023	Hercules Kombat RF - 26" Black	2	209
46				7/27/2023	Hercules Standard Black	2	210
47				7/27/2023	Hercules Streetcat Pro 24" Matt. Black Red	1	211
48				7/27/2023	Hercules Street Rider RF 26" Victor Blue	1	213
49				7/27/2023	BSA Ladybird Diva 26" Sports Purple	2	216
50				7/27/2023	BSA Ladybird Summer 26" Victor Blue	2	217
51				7/27/2023	Hercules FX - 100 DX2 3.0 26" SS Matt Black N.Orange	1	219
52				7/27/2023	BSA Champ Orbit 20" Cel. Blue	3	221
53				7/27/2023	Top Trak Challenger 20	1	222
54				7/27/2023	BSA Agent X 20" Blue Pink	2	224
55				7/27/2023	BSA Ladybird Hazel 26" Sports Purple	2	226
56				8/02/2023	BSA Ladybird Hazel 26" Pearl Pin k	12	229
57				8/04/2023	BSA Ladybird Summer 26" Victor Blue	20	234
58					BSA Ladybird Shine 26" Pearl Pink	8	235
59					BSA Ladybird Hazel 26" Sports Purple	8	236
60					Hercules Sparx 2.0 RF 24" Black	6	237
61					Hercules Tussle 26" RF Red	8	238
62					Hercules Sparx 2.0 RF 26" Victor Blue	4	239
63					BSA Trooper 20" Green Golden Yellow	2	241
64					Hercules Streetcat Pro 26" Tropper Grey	8	245
65					Hercules Wildrock IC RF 26" Black Red	2	251
66					BSA Ladybird Diva 26" Sports Purple	10	255
67				8/10/2023	BSA Ladybird Summer 26" Pink	10	258
68					Hercules Kombat R/F 24" Black	6	262
69					Hercules Standard Green	12	263
70					Hercules Wildrock IC RF 26" Black Red	6	268
71					Hercules Standard Black	12	273
72
Expected Result

 

[QuillPet]

I thought it more logical to change the column order to this. Please try it on a copy of your workbook. Could do with tidying up, but try it first

Thank you very much for the VBA code. I am really impressed that you have put much dedication into this problem. Much much appreciation.

There is a slight misunderstanding but it needs to be dealt with. The first columns of the output should be the missing values in Sheet2. i.e. values that are in Sheet1 but not in Sheet2. So, we can use 4 columns and enter every single missing value and the row number of Items in the source sheet. Again, we use 4 columns for duplicate values in Sheet1 and enter every duplicate value of source Sheet1 and the row number of Items if any. Afterward, 4 columns for duplicate values in Sheet2 in the same way. Please, modify the code to do this.

 

[kevin9999]

Thank you very much for the VBA code. I am really impressed that you have put much dedication into this problem. Much much appreciation.

There is a slight misunderstanding but it needs to be dealt with. The first columns of the output should be the missing values in Sheet2. i.e. values that are in Sheet1 but not in Sheet2. So, we can use 4 columns and enter every single missing value and the row number of Items in the source sheet. Again, we use 4 columns for duplicate values in Sheet1 and enter every duplicate value of source Sheet1 and the row number of Items if any. Afterward, 4 columns for duplicate values in Sheet2 in the same way. Please, modify the code to do this.

I don't follow you, could you show a mock up of your expected outcomes using the XL2BB add-in, then we'll both be on the same page. Tomorrow before I get to it.

 

[QuillPet]

I don't follow you, could you show a mock up of your expected outcomes using the XL2BB add-in, then we'll both be on the same page. Tomorrow before I get to it.

Can you just modify the part where the code will find the missing values of Sheet1 that are not in Sheet2? i.e. Data that are in Sheet1 but not in Sheet2 will be in the expected result. The rest works perfectly for now.

 

[kevin9999]

Thank you very much for the VBA code. I am really impressed that you have put much dedication into this problem. Much much appreciation.

There is a slight misunderstanding but it needs to be dealt with. The first columns of the output should be the missing values in Sheet2. i.e. values that are in Sheet1 but not in Sheet2. So, we can use 4 columns and enter every single missing value and the row number of Items in the source sheet. Again, we use 4 columns for duplicate values in Sheet1 and enter every duplicate value of source Sheet1 and the row number of Items if any. Afterward, 4 columns for duplicate values in Sheet2 in the same way. Please, modify the code to do this.

Afterward, 4 columns for duplicate values in Sheet2 in the same way.

OK, I think I understand the first 2, but why 4 columns for the sheet 2 data when there isn't a date column on sheet 2 - only item, quantity & (added) row number?