Excel VBA to filter rows containing same word [Closed]

kelee 4 Posts Saturday September 5, 2009Registration date September 10, 2009 Last seen - Sep 6, 2009 at 09:26 AM - Latest reply:  witsend
- Oct 30, 2011 at 09:30 AM

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!
See more 

11 replies

Best answer
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Sep 7, 2009 at 06:00 AM
Thank you
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.

It would be easierr if the main portions of the name (e.g ABC is in the beginning of each name with suffixes.

Thank you venkat1926 4

has helped 2146 users this month

kelee 4 Posts Saturday September 5, 2009Registration date September 10, 2009 Last seen - Sep 10, 2009 at 01:15 AM

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

Company Name

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.
Ward > kelee 4 Posts Saturday September 5, 2009Registration date September 10, 2009 Last seen - Nov 6, 2009 at 02:36 PM
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
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Sep 6, 2009 at 08:34 PM
Thank you
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
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
it is very simple.
use sort with ascending where you have to find the duplicates.
For eg: Sort with Company name
Just imagine
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.
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.
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...
Hi Waheed

Go for Pivot Table feature in Excel. You can find it in Data Menu -> PivotTable and PivotChart Report
or the best option would be Data->SubTotals...

it will give you something like this format..i presume you are also looking for the same
Company Value
ABC 10
ABC 15
ABC Total 25
ABV 20
ABV 10
ABV Total 30
Grand Total 55
Thank you
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?