highlight or sort the target items.

[Jdog12]

I have a list of customers in column A. Most of the customer numbers are repeated on different rows.

Now I have a separate list in column I, this list has some of the customer numbers in column A.

My job is to identify the duplicate value on both lists. It is to compare both lists and highlight the duplicate values on both lists.

I tried conditional formatting and selected duplicate, but some of the values in column A are the same, so it turned out the whole column A is highlighted. I used "equal" in conditional formatting, but I have to select each item in column I , which is time consuming.

Is there ay other method to solve this? Is VBA the only method to solve this problem?

customer 10.30.20.xlsx
	D	E	F	G	H	I
1	A	B	C		H	I
2	Customer #		Number of orders since 2019			Customer #
3	842034		255		Need data	842034
4	842034		191		for these	842038
5	8642163		191		customer #	842191
6	8642163		137			8642175
7	8642163		337			8642161
8	8642163		320			842627
9	8642163		341			842210
10	8642163		182			842005
11	8642163		74			842014
12	8642163		329			8642152
13	8642163		265			842216
14	8642163		129			842062
15	8642163		136			8642148
16	8642163		252			8642134
17	8642163		109			8642159
18	8642163		399
19	8642163		131
20	8642163		271
21	842038		150
22	842038		344
23	842038		295
24	842038		165
25	842038		314
26	842191		70
27	842191		364
28	842191		139
29	842191		352
30	842191		65
31	842191		161
32	842191		246
33	842191		286
34	842191		86
35	842191		328
36	842191		188
37	842191		289
38	842191		227
39	842191		189
40	842191		372
41	8642175		160
42	8642175		279
43	8642175		101
44	8642175		72
45	8642175		399
46	8642175		373
47	8642175		287
48	8642175		105
49	8642175		82
50	8642175		188
51	8642175		112
52	8642175		90
53	8642175		164
54	8642175		224
55	8642175		396
56	8642175		238
57	8642175		382
58	8642175		173
59	8642175		376
60	8642144		181
61	8642144		372
62	8642161		285
63	8642161		171
64	8642161		98
65	8642161		58
66	8642161		115
67	8642161		244
68	8642161		218
69	8642161		248
70	8642161		82
71	8642161		242
72	8642161		53
73	8642161		169
74	8642161		147
75	8642161		373
76	8642161		129
77	8642161		90
78	8642161		350
79	8642161		213
80	8642161		87
81	8642161		278
82	842627		179
83	842627		134
84	842627		92
85	842627		219
86	842627		219
87	842627		82
88	842627		87
89	842627		330
90	842627		275
91	842627		167
92	842627		54
93	842627		355
94	842627		93
95	842627		196
96	842627		364
97	842627		357
98	842627		172
99	842627		106
100	842210		219
101	842210		242
102	842210		90
103	842210		237
customer

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I
1				Customer #		Number of orders since 2019			Customer #
2				842034		255		Need data	842034
3				842034		191		for these	842038
4				8642163		191		customer #	842191
5				8642163		137			8642175
6				8642163		337			8642161
7				8642163		320			842627
8				8642163		341			842210
9				8642163		182			842005
10				8642163		74			842014
11				8642163		329			8642152
12				8642163		265			842216
13				8642163		129			842062
14				8642163		136			8642148
15				8642163		252			8642134
16				8642163		109			8642159
17				8642163		399
18				8642163		131
19				8642163		271
20				842038		150
21				842038		344
22				842038		295
23				842038		165
24				842038		314
25				842191		70
26				842191		364
27				842191		139
28				842191		352
29				842191		65
30				842191		161
31				842191		246
32				842191		286
33				842191		86
34				842191		328
35				842191		188
36				842191		289
37				842191		227
38				842191		189
39				842191		372
40				8642175		160
41				8642175		279
42				8642175		101
43				8642175		72
44				8642175		399
45				8642175		373
46				8642175		287
47				8642175		105
48				8642175		82
49				8642175		188
50				8642175		112
51				8642175		90
52				8642175		164
53				8642175		224
54				8642175		396
55				8642175		238
56				8642175		382
57				8642175		173
58				8642175		376
59				8642144		181
60				8642144		372
61				8642161		285
62				8642161		171
63				8642161		98
64				8642161		58
65				8642161		115
66				8642161		244
67				8642161		218
68				8642161		248
69				8642161		82
70				8642161		242
71				8642161		53
72				8642161		169
73				8642161		147
74				8642161		373
75				8642161		129
76				8642161		90
77				8642161		350
78				8642161		213
79				8642161		87
80				8642161		278
81				842627		179
82				842627		134
83				842627		92
84				842627		219
85				842627		219
86				842627		82
87				842627		87
88				842627		330
89				842627		275
90				842627		167
91				842627		54
92				842627		355
93				842627		93
94				842627		196
95				842627		364
96				842627		357
97				842627		172
98				842627		106
99				842210		219
100				842210		242
101				842210		90
102				842210		237
Master

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I2:I16	Expression	=MATCH(I2,$D$2:$D$102,0)	text	NO
D2:D102	Expression	=MATCH(D2,$I$2:$I$16,0)	text	NO

 

[Jdog12]

Thank you.

It works wonderfully!

This solution will help me a great deal at work!

 

[Fluff]

You're welcome & thanks for the feedback.