How to remove only similar matching duplicates? Or based on beginning string

[Sidoti]

I can't use xlbb add-in since my company disabled add-ins. I have a list of companies that I need to clean up the duplicates. Some company names are very similar or have a comma or period in the name or some other extra value. How can I remove them easily. I think the best way would be if the first 4 or 5 letter values match then remove it.

AAON Inc

AAON Inc.

AAON, Inc.

ABM Industries

Acasti Pharma

ACCO Brands

ACCO Brands Corporation

Acme United

Acme United Corp.

Acme United Corporation

 

[Sufiyan97]

Try

You can filter in column C everything except 1 and then delete it.

Book3
	A	B	C	D
1			Repeat
2	AAON Inc	AAON	1
3	AAON Inc.	AAON	2
4	AAON, Inc.	AAON	3
5	ABM Industries	ABM	1
6	Acasti Pharma	Acasti	1
7	ACCO Brands	ACCO	1
8	ACCO Brands Corporation	ACCO	2
9	Acme United	Acme	1
10	Acme United Corp.	Acme	2
11	Acme United Corporation	Acme	3
12
Sheet7

Cell Formulas
Range	Formula
B2:B11	B2	=LEFT(A2,IFERROR(SEARCH(",",A2),SEARCH(" ",A2))-1)
C2:C11	C2	=COUNTIF($B$2:B2,B2)

 

[Sidoti]

Thanks, but im getting a lot of errors even for companies that don't have duplicates.

BBSI	#VALUE!​
BBX Capital, Inc.	BBX Capital
Beazer Homes	Beazer
Bel Fuse	Bel
Benchmark	#VALUE!​
Benchmark Electronics	Benchmark
Benchmark Electronics, Inc.	Benchmark Electronics
Betterware.com.mx	#VALUE!​
BioSig Technologies, Inc.	BioSig Technologies

 

[Sufiyan97]

Try

edit: I have modified the formula, please check again:

Book3
	A	B	C	D
1			Repeat
2	AAON Inc	AAON	1
3	AAON Inc.	AAON	2
4	AAON, Inc.	AAON	3
5	ABM Industries	ABM	1
6	Acasti Pharma	Acasti	1
7	ACCO Brands	ACCO	1
8	ACCO Brands Corporation	ACCO	2
9	Acme United	Acme	1
10	Acme United Corp.	Acme	2
11	Acme United Corporation	Acme	3
12	Acme	Acme	4
13	BBSI	BBSI	1
14	Benchmark	Benchmark	1
15	Betterware.com.mx	Betterware.com.mx	1
16	Benchmark Electronics	Benchmark	2
17	Benchmark Electronics, Inc.	Benchmark	3
18
Sheet7

Cell Formulas
Range	Formula
B2:B17	B2	=SUBSTITUTE(LEFT(A2,SEARCH(" ",A2&" ")-1),",","")
C2:C17	C2	=COUNTIF($B$2:B2,B2)

 

[Sidoti]

I was able to get a minisheet using another computer, hopefully this is correct...

Book4.xlsx
	A
1	Company Name
2	1-800-FLOWERS.COM, Inc.
3	A10 Networks
4	AAON Inc
5	AAON Inc.
6	AAON, Inc.
7	ABM Industries
8	Acasti Pharma
9	ACCO Brands
10	ACCO Brands Corporation
11	Acme United
12	Acme United Corp.
13	Acme United Corporation
14	Actelis Networks, Inc.
15	Air Industries Group
16	Air Transport Services Group, Inc.
17	Akari Therapeutics
18	Alamo Group
19	Alamo Group Inc
20	Alamos Gold Inc.
21	Albany International
22	Alexander & Baldwin
23	Alexander & Baldwin
24	Alexander & Baldwin, Inc.
25	Alico Inc.
26	Alico, Inc.
27	ALLETE, Inc.
28	Allied Motion
29	Allied Motion Technologies
30	Altigen Communications
31	Altra Industrial Motion Corp.
32	American Battery Metals Corporation
33	American Battery Technology
34	American Finance Trust, Inc.
35	American Public Education, Inc.
36	American Shipping Company ASA
37	American Software
38	American Software, Inc.
39	American Woodmark
40	American Woodmark Corp
41	Amerigo Resources
42	Amesite Inc.
43	AMN Healthcare Services
44	Ampco Pittsburgh
45	Ampco-Pittsburgh Corporation
46	AMSC ASA
47	Anebulo Pharmaceuticals, Inc.
48	Anika Therapeutics
49	Apogee Enterprises
50	Apogee Enterprises Inc
51	Apollo Endosurgery, Inc.
52	Aqua Metals
53	ARC Document Solutions
54	ARCOSA, Inc.
55	Ardmore Shipping
56	Argentina Lithium and Energy
57	Armstrong Flooring
58	Ascent Industries Co.
59	Ashford Hospitality Trust
60	Ashford Inc.
61	Assertio Holdings, Inc.
62	Associated Capital Group, Inc.
63	Assure
64	Assure Holdings
65	Assure Neuromonitoring
66	Astec
67	Astec Industries
68	AstroNova
69	AstroNova, Inc.
70	Atlas Technical Consultants, Inc.
71	ATN International
72	ATN International, Inc.
73	Auddia Inc
74	AudioCodes
75	Audioeye
76	Average
77	Avista Corp.
78	Avista Corporation
79	Axos Financial, Inc.
80	AYRO, Inc.
81	AZZ Inc
82	AZZ Inc.
83	Backblaze Inc
84	Balchem Corporation
85	Banxa Holdings
86	Barrett Business Services Inc
87	Barrett Business Services, Inc.
88	Bassett Furniture
89	BBQ Holdings, Inc.
90	BBSI
91	BBX Capital, Inc.
92	Beazer Homes
93	Bel Fuse
94	Benchmark
95	Benchmark Electronics
96	Benchmark Electronics, Inc.
97	Betterware.com.mx
98	BioSig Technologies, Inc.
99	Bit Digital USA
100	BK Technologies
101	Black Diamond Group
102	Black Hills Corp.
103	Black Hills Corporation
104	Blue Apron
105	BLUE STAR FOODS
106	BlueCity
107	Blueknight
108	Blueknight Energy Partners
109	BlueKnight Energy Partners LP
110	BlueLinx Holdings
111	Boardwalktech Software Corp
112	Boat Rocker
113	Boat Rocker Media Inc.
114	Boomer Holdings, Inc.
115	Brady Corporation
116	Braemar Hotels & Resorts
117	BrainsWay
118	Brenmiller Energy Ltd
119	Broadwind
120	BuzzFeed, Inc.
121	Capstone Green Energy
122	Capstone Turbine Corporation
123	Carbon Streaming Corporation
124	CareCloud
125	Carriage Services, Inc.
126	Casey's General Store - Corporate Office
127	Casey's General Stores, Inc.
128	CBIZ
129	C-COM Satellite Systems Inc.
130	CEMATRIX
131	ChannelAdvisor
132	Charles & Colvard, Ltd.
133	Charles River Associates
134	Chesapeake Utilities Corporation
135	Chicago Atlantic Real Estate Finance Inc.
136	Chicago Atlantic Real Estate Finance, Inc.
137	China Xiangtai Food Co.,Ltd(New Name: Bit Origin Limited)
138	Cinedigm Corp.
139	CION Investment Corporation
140	CIRCOR International
141	Citius Pharmaceuticals, Inc.
142	Civeo Corporation
143	Climb Global Solutions
144	Co-Diagnostics
145	Columbus McKinnon Corporation
146	Comfort Systems USA
147	Comfort Systems USA, Inc.
148	Commercial Vehicle Group
149	Commercial Vehicle Group, Inc.
150	Computer Task Group
151	Computer Task Group Incorporated
152	Comstock Inc.
153	Comtech
154	Comtech Telecommunications Corp.
155	Conifex Timber Inc.
156	Connection
157	Consensus Cloud Solutions
158	Construction Partners
159	Converge Technology Solutions
160	Cooper Standard
161	CoreCard (f/k/a Intelligent Systems Corp.)
162	CoreCard Corp
163	CorEnergy Infrastructure Trust Inc
164	CorEnergy Infrastructure Trust, Inc.
165	Cornerstone Building Brands
166	Coro Global Inc.
167	Crawford & Company
168	Crawford and Company
169	Creative Media & Community Trust
170	CSG
171	CSW Industrials
172	CSW Industrials, Inc.
173	CTG
174	CTS Corporation
175	Culp, Inc.
176	Custom Truck One Source, Inc.
177	CVG
178	Cyngn Inc
179	Cyngn Inc.
180	Daktronics
181	Daktronics Inc
182	Daktronics, Inc.
183	DecisionPoint Systems
184	Deluxe
185	DHI Group Inc
186	DHI Group, Inc.
187	Diebold Nixdorf, Inc.
188	Digirad Corporation
189	DLH Holdings Corp.
190	DMC Global
191	DMC Global Inc.
192	Donnelley Financial Solutions (DFIN)
193	Douglas Dynamics
194	Dundee Precious Metals
195	Duos Technologies
196	DXP Enterprises Inc
197	Dyadic International Inc
198	Eagle Bulk Shipping Inc
199	Eagle Bulk Shipping, Inc.
200	Echo Global Logistics
201	Educational Development
202	electroCore, Inc.
203	Electromed
204	Electromed, Inc.
205	Elevate Credit, Inc.
206	eMagin Corporation
207	Emerald Expo
208	Encore Wire
209	Encore Wire Corporation
210	Energy Focus Inc.
211	Energy Recovery
212	Energy Recovery, Inc
213	Energy Recovery, Inc.
214	EnerSys
215	EnPro Industries, Inc.
216	Entravision Communications Corporation
217	Envela Corporation
218	ePlus inc.
219	EQONEX
220	ESCO Technologies
221	ESCO Technologies Inc.
222	Esports Entertainment Group
223	EV Technology Group
224	Exco Technologies Limited
225	ExOne
226	EZCORP
227	EzFill Holdings
228	Farmer Brothers
229	Farmer Mac
230	FAT Brands Inc.
231	Federal Signal Corp.
232	Federal Signal Corporation
233	FGI Industries
234	Financial Institutions, Inc.
235	Flexsteel Industries, Inc.
236	Flow Beverage Corporation
237	Flushing Financial
238	Focus Universal Inc.
239	Foraco International
240	Foresight
241	Foresight Autonomous
242	Foresight Autonomous Holdings
243	FormFactor, Inc.
244	Forrester
245	Forrester Research
246	Forrester, Inc.
247	Franklin Electric Co., Inc.
248	Freshlocal Solutions Inc.
249	FRX Innovations
250	FTI Consulting, Inc.
251	G5 Entertainment AB
252	Gaia, Inc.
253	Gain Therapeutics Inc
254	GAMCO Investors, Inc.
255	GameOn Entertainment Technologies Inc.
256	GATX
257	GATX Corp
258	GATX Corporation
259	GEE GROUP
260	Genco Shipping & Trading Limited
261	Generation Income Properties
262	Genie Energy
263	Gibraltar Industries
264	Gibraltar Industries, Inc.
265	Glatfelter Corporation
266	Global Industrial Company
267	Golden Valley Mines Ltd.
268	Good Natured Products Inc.
269	GP Strategies Corporation
270	Graham Corporation
271	Grand Canyon Education
272	Granite Construction Inc.
273	Griffon Corporation
274	Grove Inc
275	Growens S.p.A.
276	GSE Solutions
277	GSE Systems Inc.
278	GSI Technology
279	GSI Technology, Inc.
280	Guerrilla RF
281	Gulf Island Fabrication
282	Hall of Fame Resort & Entertainment Co.
283	Hammond Power Solutions Ltd
284	Harbor Custom Development, Inc.
285	Harte Hanks
286	Harte Hanks Inc
287	Haverty Furniture Companies, Inc.
288	Havertys Furniture Company
289	Havertys Furniture Company, Inc.
290	Hawaiian Holdings
291	Hawaiian Holdings, Inc.
292	Hawkins, Inc.
293	HAYNES INTERNATIONAL INC
294	Haynes International, Inc.
295	Healthcare Triangle
296	Heartcore Enterprises Inc
297	HeartCore Enterprises, Inc.
298	Heidrick & Struggles
299	Helbiz, Inc.
300	Helen of Troy Limited
301	Helius Medical Technologies, Inc.
302	Heritage Global Inc.
303	Heritage Insurance
304	Heritage Insurance Holdings Inc.
305	Heritage Insurance Holdings, Inc.
306	Herman Miller, Inc.
307	Heska Corporation
308	Hill International
309	Hill International, Inc.
310	Hillenbrand
311	Hillstream BioPharma, Inc.
312	Hippo Holdings
313	HIRE Technologies Inc.
314	HLS Therapeutics
315	HNI Corporation
316	HOOKER FURNISHINGS CORPORATION
317	HOOKER FURNITURE CORPORATION
318	Hookipa Pharma Inc.
319	Hoth Therapeutics, Inc.
320	HS GovTech ( Formerly: HealthSpace Data Systems)
321	Hudson Global
322	Hudson Global Inc.
323	Hudson Global, Inc.
324	Humacyte, Inc.
325	Huron Consulting Group
326	Hyster-Yale Materials Handling, Inc.
327	i3 Verticals LLC
328	ICF
329	IDACORP, Inc.
330	Ideal Power
331	Ideanomics
332	IDT Corporation
333	IDW Media Holdings
334	Immersion Corporation
335	Independence Contract Drilling Inc
336	Independence Contract Drilling, Inc.
337	Information Services Group
338	Information Services Group (ISG)
339	Information Services Group, Inc.
340	Infrastructure & Energy Alternatives
341	InfuSystem
342	InfuSystem Holdings Inc.
343	Infusystem, Inc.
344	Innovative Eyewear, Inc.
345	Innovative Solutions And Support
346	Insight Enterprises
347	Insperity
348	Inspira Technologies
349	Insteel Industries Inc
350	Insteel Industries, Inc.
351	Integer Holdings Corporation
352	Intellicheck
353	Intelligent Systems
354	Intelligent Systems Corp
355	InterDigital
356	InterDigital, Inc.
357	International Seaways
358	International Seaways, Inc.
359	inTEST Corporation
360	Iridium Communications Inc.
361	iSun
362	Item 9 Labs
363	J2 Global, Inc
364	Jerash Holdings (US), Inc.
365	Jerash Holdings Inc
366	John B. Sanfilippo & Son, Inc.
367	JOHNSON OUTDOORS INC
368	Kadant Inc.
369	Kaival Brands Innovations Group, Inc
370	Kelly Services Inc
371	Kelly Services, Inc.
372	Kforce Inc
373	Kforce Inc.
374	Kimball Electronics
375	Kimball Electronics Inc
376	Kimball Electronics, Inc.
377	Kimball International
378	Kimball International, Inc.
379	Kingstone Companies, Inc.
380	Kirkland's
381	Kirkland's Home
382	Kontrol Technologies Corp.
383	Kopin Corporation
384	Korn Ferry
385	Lakeland Industries
386	Lakeland Industries, Inc.
387	La-Z-Boy Incorporated
388	LEAF Mobile Inc.
389	LeMaitre
390	LeMaitre Vascular
391	Liberty Defense
392	LICT Corporation
393	LifeMD Inc
394	LifeMD, Inc
395	LifeMD, Inc.
396	Lifetime Brands Inc
397	Lifetime Brands, Inc
398	Lifetime Brands, Inc.
399	LightPath Technologies, Inc.
400	Limbach
401	Limbach Facility Services
402	Limbach Holdings Inc
403	Lincoln Educational Services
404	Lincoln Educational Services Corp
405	LiveOne, Inc.
406	LiveXLive Media, Inc.
407	Lixte Biotechnology Holdings, Inc.
408	Loop Industries
409	Loyalty Ventures
410	Loyalty Ventures Inc
411	Loyalty Ventures Inc.
412	LSB Industries
413	LSB Industries, Inc.
414	LSI Industries
415	Luvu Brands, Inc.
416	Luxfer Holdings PLC
417	LXRandCo, Inc
418	Lydall
419	Lydall, Inc.
420	MAIA Biotechnology Inc
421	MAIA Biotechnology, Inc.
422	Manchester United
423	Manitex International
424	Marpai Health
425	Mastech Digital, Inc.
426	Materion Corp
427	Materion Corporation
428	Mativ Holdings Inc
429	Mativ Holdings, Inc.
430	Matrix Service Company
431	Matson
432	Matthews International
433	Mawson Infrastructure Group Inc.
434	Mayville Engineering Company
435	McGrath RentCorp
436	MDC Partners
437	MDU Resources Group, Inc.
438	Medexus Pharmaceuticals
439	Medexus Pharmaceuticals, Inc.
440	Mednow
441	Mednow Inc.
442	Mercer International Inc.
443	Merit Medical Systems, Inc.
444	Meritage Hospitality
445	Methode Electronics
446	Methode Electronics, Inc.
447	Midwest Holding
448	Mill City Ventures III
449	Minerals Technologies Inc.
450	Minim, Inc.
451	Miromatrix Medical Inc.
452	Mistras
453	Mistras Group
454	Mitsui O.S.K. Lines Ltd
455	Modine Mfg. Company
456	Modiv
457	Mohawk Group
458	Monaker Group
459	Mondee Holdings, Inc.
460	Motus GI Holdings
461	Moving iMage Technologies
462	Moving iMage Technologies (MiT)
463	MTBC, Inc.
464	MtronPTI
465	MTS Systems Corporation
466	Mueller Industries, Inc.
467	Myomo
468	Myomo, Inc.
469	MYR Group Inc.
470	MYR Group, Inc.
471	Nanalysis Scientific Corp
472	Nanotech Security Corp.
473	Nature's Sunshine
474	Nature's Sunshine Products, Inc
475	Nature's Sunshine Products, Inc.
476	NATUZZI S.P.A.
477	Neenah Inc
478	Neenah, Inc
479	Nemaura Medical Inc
480	Neovasc Inc
481	Neovasc inc.
482	Nephros, Inc.
483	Nesco Holdings, Inc.
484	Netcapital Inc.
485	NorthWestern Corporation
486	NorthWestern Energy
487	NV5
488	NXT Energy Solutions Inc
489	NXT Energy Solutions Inc.
490	Oaktree Specialty Lending Corporation
491	Oblong Inc.
492	OLB Group Inc
493	OmniQ Corp.
494	ONE Gas, Inc.
495	OneSoft Solutions
496	OneSoft Solutions Inc.
497	OneSpan
498	OneSpan Inc.
499	Onfolio Holdings Inc.
500	Onto Innovation
501	Oportun Financial
502	ORION Group Holdings, Inc
503	Orion Group Holdings, Inc.
504	Orthofix Medical Inc
505	Osisko Gold Royalties
506	Otter Tail Corporation
507	Paltalk
508	Pangaea Logistics Solutions
509	Pangaea Logistics Solutions Ltd.
510	PAR Technology Corporation
511	PAR Technology Corporation(PAR)
512	Patriot One Technologies
513	Paysign, Inc.
514	PC Connection
515	PC Connection, Inc.
516	PC Connection, Inc. d/b/a Connection
517	PCTEL, Inc
518	Peraso Inc.
519	Perdoceo Education Corporation
520	Perdoceo Education Corporation (formerly Career Education)
521	Perion Network Ltd.
522	PetMed Express, Inc.
523	PetMed Express, Inc. d/b/a 1-800-PetMeds
524	PetMeds
525	Pioneer Power Solutions, Inc.
526	Pitney Bowes
527	Pitney Bowes Inc.
528	Playboy Enterprise, Inc.
529	Plexus
530	Plexus Corp.
531	POET Technologies Inc.
532	POET Technologies, Inc.
533	Poly (Plantronics, Inc.)
534	PopReach Corporation
535	Portland General Electric
536	Portman Ridge
537	Portman Ridge Finance Corporation
538	Potbelly Corporation
539	Powell Industries Inc
540	Powell Industries Inc.
541	Powell Industries, Inc.
542	Prenetics
543	Prestige Consumer Healthcare
544	Prestige Consumer Healthcare Inc.
545	Primoris Services Corporation
546	Propanc Biopharma Inc.
547	Prostar
548	Protech Home Medical
549	Pure Cycle
550	Pure Cycle Corporation
551	PyroGenesis Canada Inc
552	QAD
553	QAD, Inc.
554	Quaker Houghton
555	Quanex Building Products
556	Quarterhill Inc.
557	Quipt Home Medical
558	Quipt Home Medical Corp
559	Quipt Home Medical Corp.
560	RadNet, Inc.
561	Ramaco Resources, Inc.
562	Ranpak Holdings
563	Ranpak Holdings Corp
564	Raven Industries, Inc.
565	RCI Hospitality Holdings, Inc.
566	Real Luck Group
567	Rekor Systems, Inc.
568	ReneSola Ltd
569	ReneSola Power
570	Reservoir Media
571	Resources Connection
572	Resources Connection, Inc.
573	RGP
574	Richardson Electronic Ltd
575	Richardson Electronics
576	Richardson Electronics, Ltd.
577	Riley Exploration Permian, Inc.
578	Safehold Inc.
579	Sanmina Corporation
580	Sarcos Technology and Robotics Corporation
581	ScanSource, Inc.
582	Sensient Technologies Corporation
583	ServiceSource International, Inc
584	ServiceSource International, Inc.
585	SG Blocks
586	Shapeways
587	Shenzhen Semidux Chip Technology Co Ltd.
588	ShiftPixy
589	ShiftPixy, Inc.
590	Shoe Carnival
591	Shoe Carnival, Inc.
592	Sidus Space
593	Sify Technologies Limited
594	Sify Technologies Limited (NASDAQ: SIFY)
595	Silvercrest Asset Management Group Inc
596	Silvercrest Asset Management Group Inc.
597	Simpson Manufacturing Co., Inc.
598	Simpson Manufacturing Company
599	Simulations Plus
600	Simulations-Plus
601	SinglePoint Inc.
602	Slinger
603	SmartKem
604	SOBR Safe Inc
605	SOBRsafe
606	Society Pass Incorporated
607	Solar Alliance Energy
608	Solar Alliance Energy Inc.
609	Sonic Foundry
610	Sonim Technologies
611	Sono-Tek Corp
612	Southland Holdings
613	SP Plus Corporation
614	Spark Networks
615	Spark Networks, Inc.
616	Spectra7 Microsystems
617	Spire Inc.
618	Splash Beverage Group
619	SPX Corp
620	SPX Corporation
621	SPX Technologies, Inc.
622	STAAR Surgical
623	STAAR Surgical Company
624	Stabilis Solutions, Inc.
625	Staffing 360 Solutions
626	Stagwell
627	Stagwell Inc.
628	Standex International
629	Star Equity Holdings, Inc.
630	Startek, Inc.
631	Steelcase Inc.
632	Sterling Construction Company, Inc.
633	Sterling Infrastructure Inc
634	Sterling Infrastructure, Inc.
635	Strategic Education Inc
636	Strategic Education, Inc.
637	STRATTEC Security Corporation
638	Stride Inc.
639	SuperCom Ltd.
640	Superior Drilling Products
641	Superior Drilling Products, Inc
642	Superior Group of Companies
643	Surgalign
644	Surgalign Holdings, Inc. (fka RTI Surgical)
645	Surgalign Spine Technologies
646	Surmodics
647	Surmodics, Inc.
648	SuRo Capital Corp.
649	SusGlobal Energy Corp
650	SusGlobal Energy Corp.
651	SWM International
652	SWM Intl
653	Sykes Enterprises, Inc.
654	Synalloy Corporation
655	Synchronoss Technologies
656	Synlogic Inc
657	Systemax Inc.
658	Tantalus Systems
659	Tarena International Inc
660	Target Hospitality
661	Team Inc.
662	Team, Inc.
663	TechTarget, Inc.
664	Tecnoglass
665	Tecnoglass, Inc.
666	Tennant Company
667	Terran Orbital
668	Tessco Technologies
669	Tessco Technologies, Inc.
670	Tetra Tech
671	Tetra Tech, Inc.
672	The Alkaline Water Company
673	The Arena Group
674	The Eastern Company
675	The Gorman-Rupp Company
676	The Marketing Alliance
677	The Marygold Companies Inc
678	The Metals Company
679	The Necessity Retail REIT
680	The ODP Corporation
681	The Real Brokerage
682	The Real Brokerage Inc
683	The Shyft Group
684	The Shyft Group, Inc. (Spartan Motors)
685	Theriva Biologics, Inc.
686	Thunderbird Entertainment
687	Tinybeans
688	Titan International, Inc.
689	Tivic Health Systems, Inc.
690	Tonix Pharmaceuticals Holding Corp.
691	ToughBuilt Industries, Inc.
692	Townsquare Media Inc
693	Transcat
694	Transcat, Inc.
695	Transcode
696	Trecora Resources
697	Trinity Biotech
698	Troika Media Group
699	TrueBlue
700	TTEC Holdings Inc.
701	TTEC Holdings, Inc.
702	Tupperware Brands Corp
703	Tupperware Brands Corporation
704	Turning Point Brands
705	U.S. Concrete, Inc.
706	U.S. Energy Corp
707	U.S. Energy Corporation
708	U.S. Physical Therapy
709	u-blox Holding AG
710	UFG Insurance
711	UFP Industries
712	UFP Industries, Inc.
713	UFP Technologies, Inc.
714	UGE International
715	UNIFI
716	Unisys
717	Unisys Corporation
718	Universal Electronics
719	Universal Electronics Inc
720	Universal Electronics, Inc.
721	Universal Technical Institute
722	Universal Technical Institute, Inc.
723	Ur-Energy Inc.
724	US Concrete Inc.
725	USANA Health Sciences
726	USD Partners LP
727	Varex Imaging
728	Varex Imaging Corp.
729	Venus Concept Inc.
730	Verb Technology Company, Inc.
731	VerifyMe
732	Veritiv
733	Veritone, Inc.
734	VersaBank
735	Vertex Energy
736	Vertical Capital Income Fund
737	VIA Optronics
738	Viad Corp
739	Victory Square Technologies
740	VIQ Solutions Inc.
741	Vishay Precision Group
742	Vision Marine Technologies
743	Vivakor
744	VolitionRx
745	VolitionRx Limited
746	Vox Royalty
747	Vox Royalty Corp.
748	VOXX International Corporation
749	Voyager Digital
750	VSE Corporation
751	Wayside Technology
752	WidePoint Corporation
753	Willdan
754	Willdan Group
755	Williams Industrial Services Group
756	Winnebago Industries
757	Wireless Telecom Group
758	XCEL Brands
759	XORTX Therapeutics, Inc.
760	Xperi Holding Corporation
761	YATRA Online Inc
762	Yatra Online Inc.
763	Zedge
764	ZEN Graphene Solutions
765	Zentek
766	Zomedica Inc
767	Zoom Telephonics dba "Minim"
Sheet1

 

[Sufiyan97]

Try

Book3
	A	B	C	D
1			Repeat
2	1-800-FLOWERS.COM, Inc.	1-800-FLOWERS.COM	1
3	A10 Networks	A10	1
4	AAON Inc	AAON	1
5	AAON Inc.	AAON	2
6	AAON, Inc.	AAON	3
7	ABM Industries	ABM	1
8	Acasti Pharma	Acasti	1
9	ACCO Brands	ACCO	1
10	ACCO Brands Corporation	ACCO	2
11	Acme United	Acme	1
12	Acme United Corp.	Acme	2
13	Acme United Corporation	Acme	3
14	Actelis Networks, Inc.	Actelis	1
15	Air Industries Group	Air	1
16	Air Transport Services Group, Inc.	Air	2
17	Akari Therapeutics	Akari	1
18	Alamo Group	Alamo	1
19	Alamo Group Inc	Alamo	2
20	Alamos Gold Inc.	Alamos	1
21	Albany International	Albany	1
22	Alexander & Baldwin	Alexander	1
23	Alexander & Baldwin	Alexander	2
24	Alexander & Baldwin, Inc.	Alexander	3
25	Alico Inc.	Alico	1
26	Alico, Inc.	Alico	2
27	ALLETE, Inc.	ALLETE	1
28	Allied Motion	Allied	1
29	Allied Motion Technologies	Allied	2
30	Altigen Communications	Altigen	1
31	Altra Industrial Motion Corp.	Altra	1
32	American Battery Metals Corporation	American	1
33	American Battery Technology	American	2
34	American Finance Trust, Inc.	American	3
35	American Public Education, Inc.	American	4
36	American Shipping Company ASA	American	5
37	American Software	American	6
38	American Software, Inc.	American	7
39	American Woodmark	American	8
40	American Woodmark Corp	American	9
Sheet7

Cell Formulas
Range	Formula
B2:B40	B2	=SUBSTITUTE(LEFT(A2,SEARCH(" ",A2&" ")-1),",","")
C2:C40	C2	=COUNTIF($B$2:B2,B2)

 

[Sufiyan97]

IGNORE previous and Try this:

Book3
	A	B	C	D
1			Repeat
2	1-800-FLOWERS.COM, Inc.	1-800-FLOWERS.COM I	1
3	A10 Networks	A10 N	1
4	AAON Inc	AAON I	1
5	AAON Inc.	AAON I	2
6	AAON, Inc.	AAON I	3
7	ABM Industries	ABM I	1
8	Acasti Pharma	Acasti P	1
9	ACCO Brands	ACCO B	1
10	ACCO Brands Corporation	ACCO B	2
11	Acme United	Acme U	1
12	Acme United Corp.	Acme U	2
13	Acme United Corporation	Acme U	3
14	Actelis Networks, Inc.	Actelis N	1
15	Air Industries Group	Air I	1
16	Air Transport Services Group, Inc.	Air T	1
17	Akari Therapeutics	Akari T	1
18	Alamo Group	Alamo G	1
19	Alamo Group Inc	Alamo G	2
20	Alamos Gold Inc.	Alamos G	1
21	Albany International	Albany I	1
22	Alexander & Baldwin	Alexander &	1
23	Alexander & Baldwin	Alexander &	2
24	Alexander & Baldwin, Inc.	Alexander &	3
25	Alico Inc.	Alico I	1
26	Alico, Inc.	Alico I	2
27	ALLETE, Inc.	ALLETE I	1
28	Allied Motion	Allied M	1
29	Allied Motion Technologies	Allied M	2
30	Altigen Communications	Altigen C	1
31	Altra Industrial Motion Corp.	Altra I	1
32	American Battery Metals Corporation	American B	1
33	American Battery Technology	American B	2
34	American Finance Trust, Inc.	American F	1
35	American Public Education, Inc.	American P	1
36	American Shipping Company ASA	American S	1
37	American Software	American S	2
38	American Software, Inc.	American S	3
39	American Woodmark	American W	1
40	American Woodmark Corp	American W	2
41	Amerigo Resources	Amerigo R	1
42	Amesite Inc.	Amesite I	1
43	AMN Healthcare Services	AMN H	1
44	Ampco Pittsburgh	Ampco P	1
45	Ampco-Pittsburgh Corporation	Ampco-Pittsburgh C	1
Sheet7

Cell Formulas
Range	Formula
B2:B45	B2	=SUBSTITUTE(LEFT(TRIM(A2),SEARCH(" ",TRIM(A2)&" ")+1),",","")
C2:C45	C2	=COUNTIF($B$2:B2,B2)

 

[Sidoti]

This is working better. Any way to add the second word in string to filter since companies with same first word are being filtered. so like:

American Battery Metals Corporation	American	1​
American Battery Technology	American	2​
American Finance Trust, Inc.	American	3​
American Public Education, Inc.	American	4​

Black Diamond Group	Black	1​
Black Hills Corp.	Black	2​
Black Hills Corporation	Black	3​

 

[Sidoti]

Hah thats actually better. Still some issues but I can spot them easily after

AstroNova	AstroNova	1​
AstroNova, Inc.	AstroNova I	1​

Benchmark	Benchmark	1​
Benchmark Electronics	Benchmark E	1​
Benchmark Electronics, Inc.	Benchmark E	2​

 

[Sufiyan97]

Glad you got solution at some extent.