How to obtain duplicate word where the each cells in one column are text strings?

[sinapabs]

In one column, here are the sample input which goes on until about 4,000 rows:



ALIANZA ESTRATEGICA
BOTTARI SPA VIA TAVOGLOERE
BUNZL
MEDIDIS SA
SOCIETE HENKEL
3 Susses
3M BKK
3M
3M
3m
3M - BOND
3M HKG
3M - INNOCOM
3M - MEANING
3M - NARITA
3M - PAID
3M - PODA
3M - TRANS
3M ( TJ )
3M ANR
3M ASIA
3M ASIA
3M Asia Pacific
3M ASIA PACIFIC RDC
3M Asia Paciic
3M AU
3M AUSTIN
3M Australia
3M Belgium
3M CANADA
3M CANADA INC
3M CARMONA
3M China
3M CONSOL
3M DAMING
3M DEUTSCHLAND
3M DEUTSCHLAND
3M EDC
3M ELECTRO & COMMUNICATION
3M ESPANA S.A.
3M France
3M FRANCE
3M FRANCE CENTRE
3M FRANCE CONSOL
3M Germany
3M GULF
3M GULF
3M Health
3M HEALTH CARE
3M HEALTHCARE
3M HKG
3M HONG KONG
3M Hongkong
3M INNOVATION
3M INNOVATION SINGAPORE PTE LTD
3M INNOVATION SINGAPORE PTE LTD (TUAS)
3M INNOVATION SINGAPORE PTE LTD (WDLAND)
3M INT
3M International
3M INTL
3M INT'L
3M INTL TRADING(SH) CO LTD
3M IT
3M ITALIA
3M ITALIA S.P.A
3M ITALY
3M KOREA
3M KOREA LIMITED
3M LTD
3M MASKING
3M MATERIAL
3M MATRA
3M MAY
3M Medical
3M NEW ZELAND
3M PERU
3M PERU S.A.
3M Phillippines
3M SEREMBAN
3M Shanghai
3M SUZHOU
3M Switzerland
3M TAIWAN
3M TAIWAN CONSOL
3M TEC
3M TECH
3M TECHNOL
3M TECHNOLOGIES SINGAPORE PTE LTD
3M TH
3M THAILAND
3M -YAT FAI
3M, ISABERG
3M, MONSTER
3M, TYCO
3M,ACCO
3MGULF
ALLIED-3M
DSG - 3M
HANA - 3M
PT. 3M
PT. 3M INDONESIA
TAISUN - 3M
3NOD GROUP
A &M
A MEDIA
A NOVO
A-NOVO
A TELEKOM
A&M
A&M (MIDDLE EAST)
A&N
A&R
A&S
A.I
A.I C/O OHL
A.KLOVAS COMPANY
A.O. SMITH
A.S TITANIUM
A.S. TIT
A.S. Titanium
A.S. TITANUM LTD
A.S.TITANIUM LTD.
A.S.TITANUM
A+P Trading
AART DECORATIONS
AARTS
AARTS DECORATIONS
AARTS DECORATIONS BVBA
AAZ-PLV
Abans
ABB
ABB IND.
ABC ACCENT FOOTWEAR PTY
ABCACCM/ RINPASM
ABCDECO
ABDUL KARIM
ABDULHAMEED SHRAYKI
ABLE
ABLE
ABLE PLASTIC
ABLE PLASTICS
Abric
AC IMPORTS
ACBEL
ACCENT
ACCENT CONSOL
ACCENT BUILDING
ACCENT CONSOL
ACCENT GROUP
ACCENTURE
ACCESSOIRE
ACCESSOIRES & STOFFE KOLN GMBH
ACCESSOIRES & STOFFE KOLN GMBH
ACCESSOIRES&STOFFE KOLN GMBH
ACCESSORIES & STOFFE KOELN
ACCI
ACCO
ACCO / PCE
Acco Brands
ACCO BRANDS MECICANA
ACCO, 3M,CSB
ACCO,CSB
ACCTON
ACCTON LOGISTICS
ACCURIDE
ACKNIT INDUSTRIES
ACP
ACP WOOD
ACSUD
ACSUD BP
ACSUD, WALIBI
ACT
ACT IMPORTS
Actebis
ACTIS GLOBAL
ACUITY
ACUMENT
ACUMNET
ADECLU
ADIMEX
ADIMEX-MULTIPROFIL
ADIMEX-MULTIPROFIL LTD
ADM COCOA
ADOCEAN
ADOR FONTECH
ADVENTA HEALTH
ADVICE
ADVICE ELECTRONICS
AFAK
AFC
AFE
AFE METAL
AFE METAL
AGAMA
AGARWAL INDUSTRIES
AGI
AGILITY
AGRI
Ai Rashed Int
AIC International
AIK CHEONG
AIR CARE CO
AIR COMFORT PRODUCTS
Air International
AIR INTERNATIONAL
Air Intnernational
AIR PACIFIC
AIR TEMP DE MEXICO, S,A. DE C.V.
AIRTIES KABLOSUZ
AJAX SINMALCO
AKL CONSOL
AKO-AGRARTECHNIK
AKSH TECHNOLOGIES
AKZO
AKZO NOBEL NOYA A.S.
AL HAMLI
AL HAMLI
AL MARIAH
AL SOLHIN
AL SOLIHIN
AL TAQADDOM PHARMA
ALAIN ST CARBEL
ALBION
ALCATEL
ALCON
ALE SPAN SILS
ALE SPAN SILS
ALFONSO
ALGODONERA
AL-KO RECORDS
AL-KO RECORDS S.A.
ALL IN GAMES
ALL PRO FITNESS
ALLIAN
ALLIANCE
ALLIBERT HOME
ALLIED CENTRIFUGAL
ALLINOX
ALLOY AMERICA
ALMA TRADING
ALMOAYED WIHELMSEN LTD
ALOGO
ALPHA
ALPHA
ALPHA METAL
ALPs Ind.
ALRAY ALUMINUM
ALTEC LANSING
ALWAFEER AIR
ALWAQAE
AM INTERNATIONAL CO.,LTD
AM NETWORKS
AMAZON
AMAZON EU S.A.R.L.
AMAZON.EU S.A.R.L
AMBER
Amber Packaging
AMBER PACKAGING CORPORATION
AMC Grupo
AMEDIA (SWIFT VALUE)
AMEDIA OFFICE SUPPLIES
AMEFA
AMEFA STAHLWAREN
AMEFA STAHLWAREN GMBH
AMERICA
AMERICA TRIM
AMERICAN BILTRITE
AMERICAN POWER
AMERICAN PRODUCTS
American Standard
AMSTD
AMERICAN TRIM
AMETISTA ESTOFADOS LTDA
Amex Technology
AMITICO
AMPCO
AMPCO
AMPHENOL OMNICONNECT
AMRAN
AMTICO
AND IMPEX
ANDANI
Anderans Hei
ANDRADE
ANDREA
ANDREW
ANGLO(SPOJENE)
A-NOVA
ANR CONSOL
ANTANIA
ANTEREST SCEDER
ANTONIO
AOC Int'l
APACHE
APC
APC CONVERSION
APCIS
APCIS
APERTO
APEX
APEX MEDICAL S.L.
APEX, BELLOTA
APEXINTEC
APLHA ASSOCIATES
APM
APPENDAG
APPENDAGE
APPENDAGE
APPLE
APPLICA
Applica Manufacturing
APPLICANT
AQUA CLOR
AQUARELA
AQUATUNING GMBH
AR
ARAB PAPER
ARABIN CALIBRATION LLC
ARDEE
ARDEX S'PORE
ARGUS
ARIOLE
ARMADA
ARMADA BILGISAYAR
ARORA
ARRIS
ARROW
ARROW CONSOL
ARROW CONSOL
ART GUILD
ARTESANOS SA
ARTESCO
ARTINO
ARVATO
ARVIL
ARVMER
ASA TOYS
ASAHI DM
ASEA
ASH CONSOL
ASIA KITCHEN
ASIA PACIFIC
ASIA PACIFIC
ASIA VITAL
ASIA VITAL COMPONENTS
ASIAN
Asian Food
ASIAN FOOD WHOLESALER
Asian Foods
ASIAN KITCHEN
ASK
ASSEMBLY AND PACHAINA
ASTEC
ASTEC AMERICA
ASTON GLOBAL INC
ASTRA
ASTRAL
ASUKU CO.,LTD.
ASUS
ASVAtec
ASYA KATILIM
ATESANOS
ATHENAS
ATHENAS INCORPORADORA
ATIK PASHA
ATIL
ATIL ELEKTRONIK
ATIL ELEKTRONIK SAN TIC
ATLANTA EARTHWORKS
ATLANTIC
ATLANTIC APPAREL
ATLAS
AU OPTRONICS
AUDIO TECHNOLOGIES
AUDIO Y SONIDO
AUDREY
AUO
AUSTELSERM
AUSTRALIAN EMBASSY
AUSTRIAN
AUSTRIAN GAMING
AUTBAK
AUTO CHINA DEL PERU
AUTO TECH
AUTOTECH
AUTOTECH INDUSTRIES
AUTOLIV
Autoliv Korea
AUTRANS
AUXILMAT
AUXINB LOGISTIC
AUXINE
AUXINE LOGISTICS
AUXINE LOGISTICS
AVC
AVC
AVJ ELECTRIC
AVON
AVON AUSTRALIA PTY LTD
AVON BEAUTY
AVON BEAUTY PRODUCTS
Avon Colombia
AVON COLOMBIA LTDA
Avon Cosmeticos
AVON COSMETICS
AVON COSMETICS MANUF S RL CV
AVON THAI/RUBIA
AVON VIETNAM
COSMETICOS
COSMETICOS AVON S.A.
COSMETICOS AVON SA
JUSTIN AVON
JUSTINE AVON
Productos Avon
AWOX
AXER
AXER SHOE
AXER SHOE B.V.
AXER SHOES B.V.
AXLETCH
AXLETECH INTERNATIONAL
AXXION
AZKO NOBEL COATING
B&B
B&D
B. SIRIUS
B.E International
B.E INTERNATIONAL FOODS B.V
B.E.
BE
BE INTERNATIONAL
BE INTL
BE INT'L
BE INT'L
B/R/K
B/R/K VERTRIEBS-GMBH
BABY
BABY LOVE
BABY PRODUCTS S.A.
BAD TOYS
BADGER GIFTWARE
BAEUMLER GMBH & CO
BAGATELLE
BAHRI & MAZROI
BAILEY
BAJAJ
BAJAJ ELECTRICAL
BAJAJ ELECTRICALS
Bajaj Carpet
BAKER
BAKER ATLAS
BAKER HUGHES
BAKER OIL
BAKER OIL TOOLS
BAKER HUGHERS
BAKER HUGHES INTEQ
BAKER INTEQ
BAKHUGI
BAKKAH
BALLIU GARDEN FURNITURE
BALTEC
BANCO
BANCO SABADELL
Banyantel Central
BANZLE
BAOFENG
BAR MAGEN
BARDELLI
BARDELLI GUALTERIO LUIS
BARRINGTON
BARRINGTON TEA
BASFOUR 2436(PTY) LTD
Bata

 

[bobsan42]

short for words?

 

[al_b_cnu]

Excel Workbook
	A	B
1	Text	Duplicate?
2	ALIANZA ESTRATEGICA
3	BOTTARI SPA VIA TAVOGLOERE
4	BUNZL
5	MEDIDIS SA
6	SOCIETE HENKEL
7	3 Susses
8	3M BKK
9	3M
10	3M	<===
11	3m	<===
12	3M - BOND
13	3M HKG	<===
Sheet1

Excel 2003

Cell Formulas
Range	Formula
B2	=IF(COUNTIF(A:A,A2)<>1,"<===","")
B3	=IF(COUNTIF(A:A,A3)<>1,"<===","")
B4	=IF(COUNTIF(A:A,A4)<>1,"<===","")
B5	=IF(COUNTIF(A:A,A5)<>1,"<===","")
B6	=IF(COUNTIF(A:A,A6)<>1,"<===","")
B7	=IF(COUNTIF(A:A,A7)<>1,"<===","")
B8	=IF(COUNTIF(A:A,A8)<>1,"<===","")
B9	=IF(COUNTIF(A:A,A9)<>1,"<===","")
B10	=IF(COUNTIF(A:A,A10)<>1,"<===","")
B11	=IF(COUNTIF(A:A,A11)<>1,"<===","")
B12	=IF(COUNTIF(A:A,A12)<>1,"<===","")
B13	=IF(COUNTIF(A:A,A13)<>1,"<===","")

 

[sinapabs]

No it is not sufficient because I do not only want to get the "exact" duplicate but specifically a word in a string. For example:


ACCENT
ACCENT CONSOL
ACCENT BUILDING
ACCENT CONSOL
ACCENT GROUP

I wanted to get the word ACCENT and be able to paste it in the next column.

 

[al_b_cnu]

Not sure that I understand you.

Can you give an example?