Excel VBA to filter rows containing same word

Closed
Report
Posts
4
Registration date
Saturday September 5, 2009
Status
Member
Last seen
September 10, 2009
-
 witsend -
Hello,

In my Excel there is a column, say, column A, where all the cells are text string such as company names. My task is to filter all the rows where the cells in column A contain the same company names. However, my problem is the company names was originally entered from different sources therefore for the same company it may be entered in different forms, such as for company ABC, it might be entered like ABC CO, ABC PTY LTD, or COMPANY ABC. There are also many other companies entered with the same problem. What I need to do is to find any word in Column A that shows up twice or more and filter all the rows that contains such cells in Column A.

I know this is a very hard task but just wondering if it is possible to do in in VBA? Your help would be greatly appreciated!

3 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
I suppose it can be done by a macro. what you have to do is to extract a list of basic names of the companies in your database and filter and copy one by one by using a macro by automation. . For this you have to post small extract of your database and then one can create the macro.
greetings.

It would be easierr if the main portions of the name (e.g ABC is in the beginning of each name with suffixes.
4
Posts
4
Registration date
Saturday September 5, 2009
Status
Member
Last seen
September 10, 2009

Hi,

Below is an example for which I want to filter for the duplicate words.

Company Name
ABC
ABC LTD
ABC CO
IBM
ING ENGINEERING
DDT
MAG ABC
ING CO
MMM
BAG CO
BNN LTD
MMM LTD

Suppose this is Column A, and A1 is the heading "Company Name". The main word I'm looking up is always the first one word in each cell. As you can see, the word "ABC" appears three times in A2, A3 and A4, and "ING" appears twice in A6 & A9, and "MMM" twice on A10 and A13.

Can anyone create a macro that can filter all the above rows which contains a word that appreas twice or more in Column A?

Thanks a lot in advance as this will save me heaps of time and pains for this job as I'm currently doing this with my eyes and I have hundreds of lines to look at each month.
0
>
Posts
4
Registration date
Saturday September 5, 2009
Status
Member
Last seen
September 10, 2009

I found this on Ozgrid.com - works for me

Sub RemoveDupes()

Columns(1).EntireColumn.Insert 'Add extra Column, "A" becomes "B"
Range("B1", Range("B65536").End(xlUp)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("A1"), Unique:=True 'Filter out duplicates and copy unique list to "A"
Columns(2).EntireColumn.Delete 'Add extra Column, "B" becomes "A"

End Sub
0
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
use wildcards

the column A must have column heading in A1
click data=filter-autofilter
click the arrow in column A heading
click custom
left hand side window choose "equal to"
in the right hand side window type
*ABC*
click ok
do you get what you want.
you can copy the filtered data anywhere

to remove filter click again data filter autofitler

* is the sign of wild card
the two * on both sides indicates any other characters or numbers before and after.
wildcards can be used only for some functins in excel
0
Hi Vencat1926,

Thanks for your answer to my question. However, "ABC" is only an example in my question, actually there are some other company nams in column A. What I wanted to filter is not just ABC, but all the company names that apprears twice or more in Column A, and I want to filter them at one go, not just to filter them one by one (actually before I do the filter, I will have not idea what word to look up, but just want to find any words that apprear more than once in column A. Is there any way to work this out?

Thanks
0
it is very simple.
use sort with ascending where you have to find the duplicates.
For eg: Sort with Company name
Just imagine
A1 = ABC PVT LTD
A2 = ABC PVT LTD
Now write formula in A2 as =A2=A1 Then Press enter
Then your will get true in A2 cell
Then copy this formula and drag it down, you will find duplicate data as true
Then use filter option and filter with the word TRUE
Then Delete all the True Datas.
Now Duplicate Datas are gone.
0
wow Prem...You are a genius!!!! I was trying to figure this out all day. Thanks! Just Sort the cloumn from A to Z. Then use the eqaution if(A2-A1,0,A1)! The duplicates will show the number 0.
0
Hi. I'm looking for the method that:
How I can sort and take sum in one step, that is:
Column A contains companies, Column B Contains values. I want to sort all by Company Names, and after every group of companies there should be total in the next row. For example sum of the values of company ABC, then DEF, then GHI and so on...
0
Hi Waheed

Go for Pivot Table feature in Excel. You can find it in Data Menu -> PivotTable and PivotChart Report
0
I'm a non-VBA user too, hoping to benefit from macros.
I'm going to record quite a detailed a macro to convert data to produce club results in a regional race; at a late stage of processing, it is necessary to sort by Club column and then delete all rows other than my club as in 'if C$2 not = 'CH' then delete row'.
Can the previous macro on this post be adapted easily to meet this need?

Thanks

witsend
0