Highlight and delete duplicates items in the different spaces for multiple columns

abdo meghari

Well-known Member
Joined
Aug 3, 2021
Messages
651
Office Version
  1. 2019
Hello
I have many items in column A . I want compare the items in column A with the others columns , if they are duplicated items & and matching the same thing ( should be the same spaces and the same letters sizes ) then should delete the duplicates items . and if there is different spaces and different letters sizes then shouldn't delete it and considers new item should highlight by red as in result in sheet output. short words when matching the items should be sensitive for letters & spaces .
I put a little items in sheet output to understand how result should be . data could be 1400 rows .
thanks


REMOVE (1).xlsm
ABCD
1BRANDBRANDBRANDBRAND
2BS 1200 R20 18PR G580 JAPBS 1200 R20 18PR G580 JAPBS 13 R22.5 R187 JAPBS 1200 R20 18PR G580 JAP
3BS 1200 R20 18PR G580 THIBS 1200 R20 18PR G580 THIBS 1400R20VSJ TCF JAPBS 1200 R20 18PR G580 THI
4BS 1200 R24 G582 JAPBS 13 R22.5 R187 JAPBS 175/70R14 EP150 THIBS 13 R22.5 R187 JAP
5BS 13R22.5 R187 JAPBS 1400R20VSJ TCF JAPBS 185/65R14 EP150 INDBS 1400R20 VSJ TCF JAP
6BS 1400R20VSJ TCF JAPBS 175/70R13 EP150 THIBS 185/65R15 B250 JAPBS 155 R12C R624 INDO
7BS 155 R12C R624 INDOBS 175/70R14 EP150 THIBS 185/65R15 T01 JAPBS 175/70R13 EP150 THI
8BS 185/65R14 EP150 INDOBS 185/65R14 EP150 INDOBS 195/55 R16 EP300 THIBS 175/70R14 EP150 THI
9BS 185/65R15 B250 JAPBS 185/65r15 B250 JAPBS 195/60 R15 EP150 THABS 185/65R14 EP150 INDO
10BS 185/65R15 T01 JAPBS 195/55 R16 EP300 THIBS 195/60R15 T001 JAPBS 195/55 R16 EP300 THI
11BS 195/60 R15 EP150 THIBS 195/75R16C R613 JAPBS 195/70R15C R623 JAPBS 195/60R15 T001 JAP
12BS 195/65R15 EP15 JAPBS 195R14C 613 JAPBS 195/75R16C R613 JAPBS 195/75R16C R613 JAP
13BS 195R14C 613 JAPBS 195R14C R623 THIBS 195R14C 613 JAPBS 195R14C 613 JAP
14BS 195R15C 613V JAPBS 195R15C 613V JAPBS 195R15C 613V JAPBS 195R14C R623 THI
15BS 195R15C R623 THIBS 195R15C R623 THIBS 195R15C R623 THIBS 195R15C 613V JAP
16BS 205/60R16 T001 JAPBS 205/65 R16 EP300 INDOBS 205/60R16 T001 JAPBS 195R15C R623 THI
17BS 205/65 R16 EP300 INDOBS 205/65R15 EP150 INDOBS 205/65 R16 EP300 INDBS 205/60R16 T001 JAP
18BS 205/65R15 T005 THIBS 205/65R15 T005 THIBS 205/65R15 T005 THIBS 205/65 R16 EP300 INDO
19BS 205/70R15C R623 THIBS 205/70R15C R623 THIBS 205/70R15C R623 THIBS 205/65R15 T005 THI
20BS 205R16C D840 THIBS 205R16C D840 THIBS 205R16C D840 THIBS 205/70R15C R623 THI
21BS 215/50 R17 EP300 THIBS 215/50 R17 EP300 THIBS 215/55 R16 EP300 INDBS 205R16C D840 THI
22BS 215/55R17 GR90 INDOBS 215/55 R16 EP300 INDOBS 215/55R17 GR90 INDBS 215/50 R17 EP300 THI
23BS 215/70R15C R623 THIBS 215/55R17 GR90 INDOBS 215/60R16 EP300 THIBS 215/55 R16 EP300 INDO
24BS 225/40R18PR 050A JAPBS 215/60R16 EP300 THIBS 215/70R15C R623 THIBS 215/70R15C R623 THI
25BS 225/60R16 T001 JAPBS 215/70R15C R623 THIBS 225/55 R16 EP300 INDBS 225/60R16 T001 JAP
26BS 225/70R15C R623 JAPBS 225/40R18PR 050A JAPBS 225/55R17 EP300 THIBS 225/70R15C R623 JAP
27BS 225/70R16 D687 JAPBS 225/55R17 EP300 THIBS 225/60R16 EP300 INDBS 225/70R16 D687 JAP
28BS 225/70R16 H005 THIBS 225/60R16 T001 JAPBS 225/70R15C R623 JAPBS 225/70R16 H005 THI
29BS 235/55 R17 T005 THIBS 225/70R15C R623 JAPBS 225/70R16 H005 THIBS 235/55 R17 T005 THI
30BS 245/45R17 T05A JAPBS 225/70R16 H005 THIBS 245/70R16 D684 THIBS 235/55R17 ER300 JAP
31BS 245/45R18 EP300 THIBS 235/55 R17 T005 THIBS 245/70R17 684A JAPBS 235/60R16 T01 JAP
32BS 245/75R17 693A JAPBS 235/60R16 T01 JAPBS 245/75R17 693A JAPBS 235/60R18 D-SPORT JAP
33BS 255/70R15C D840 THIBS 245/40R18PR 050A JAPBS 255/70 R16 D697 THIBS 245/70R16 D684 THI
34BS 265/65R17 D840 JAPBS 245/45R18 EP300 THIBS 255/70R15C D840 THIBS 255/70R15C D840 THI
35BS 265/70R16 D697 THIBS 245/75R17 693A JAPBS 265/50R20 DHPA JAPBS 265/60R18 D840 JAP
36BS 315/80R22.5 R152 JAPBS 255/70R15C D840 THIBS 265/65R17 D840 JAPBS 265/65R17 D840 JAP
37BS 315/80R22.5 R184 JAPBS 265/65R17 D840 JAPBS 275/40R20 Dsport JAPBS 275/40R20 Dsport JAP
38BS 325/95R24 G582 JAPBS 265/70R16 D697 THIBS 275/65R18 AL01 JAPBS 285/65R17 R683 JAP
39BS 385/65 R22.5 R164 JAPBS 315/80R22.5 R184 JAPBS 315/80R22.5 R184 JAPBS 315/80R22.5 R184 JAP
40BS 425/65R22.5 R164 JAPBS 315/80R22.5 R184 THIBS 385/65 R22.5 R164 JAPBS 315/80R22.5 R184 THI
41BS 750R16 R230 TCF JAPBS 315/80R22.5-18PR G580 JAPBS 385/65 R22.5 R164 THABS 385/65 R22.5 R164 JAP
42BS 750R16 VSJ TCF JAPBS 385/65 R22.5 R164 JAPBS 750R16 R230 TCF JAPBS 385/65 R22.5 R164 THI
43DT 315/80r22.5 DS50 THIBS 385/65 R22.5 R164 THIBS 750R16 VSJ TCF JAPBS 425/65R22.5 R164 JAP
44DT 385/65r22.5 DT40 THIBS 750R16 R230 TCF JAPDT 315/80r22.5 DS50 THIBS 650R16 R230 JAP
45BS 1200R24 G580 JAPBS 750R16 VSJ TCF JAPFS 215/55R17 TZ700 JAPBS 750R16 R230 TCF JAP
46BS 255/70R16 H005 THIDT 315/80r22.5 DS50 THIFS 225/45R17 TZ700 JAPBS 750R16 VSJ TCF JAP
47BS 265/70R16 D840 THIDT 385/65r22.5 DT40 THIFS 31*10.50R15 DEST AT INDDT 315/80r22.5 DS50 THI
48BS 275/70R16 H005 THIFS 205/65R15 TZ700 JAPFS 215/60R16 ROADHAWK ITALYDT 385/65r22.5 DT40 THI
SHEET1




REMOVE (1).xlsm
AB
1ITEMBRAND
21BS 1200 R20 18PR G580 JAP
32BS 1200 R20 18PR G580 JAP
43BS 1200 R20 18PR G580 THI
54BS 1200 R24 G582 JAP
65BS 13R22.5 R187 JAP
76BS 13 R22.5 R187 JAP
87BS 13 R22.5 R187 JAP
98BS 13 R22.5 R187 JAP
109BS 1400R20VSJ TCF JAP
1110BS 1400R20 VSJ TCF JAP
1211BS 155 R12C R624 INDO
1312BS 185/65R14 EP150 INDO
1413BS 185/65R15 B250 JAP
1514BS 185/65r15 B250 JAP
OUTPUT
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Forum statistics

Threads
1,224,830
Messages
6,181,229
Members
453,026
Latest member
cknader

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top