Excel VBA to filter rows containing same word [Closed]

Ask a question kelee 4Posts Saturday September 5, 2009Registration date September 10, 2009 Last seen - Last answered on Oct 30, 2011 at 09:30 AM by witsend

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!
plus moins
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.
Was this answer helpful?  
kelee 4Posts 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- Nov 6, 2009 at 02:36 PM
I found this on - 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
plus moins
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
Prem Kumar- Apr 24, 2010 at 01:10 AM
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.
Shukree- Sep 1, 2010 at 09:10 PM
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.
Waheed- Oct 7, 2010 at 12:05 AM
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...
Jimmy Sunny- Feb 3, 2011 at 04:22 PM
Hi Waheed

Go for Pivot Table feature in Excel. You can find it in Data Menu -> PivotTable and PivotChart Report
Jimmy Sunny- Feb 3, 2011 at 04:26 PM
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
plus moins
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?



Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!