Kioskea
Recherche
Ask a question Report

Excel VBA to filter rows containing same word [Closed]

kelee 4Posts Saturday September 5, 2009Registration date September 10, 2009Last seen - Latest answer on Oct 30, 2011 09:30AM
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!
Read more 
Answer
+4
moins plus
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.

kelee 4Posts Saturday September 5, 2009Registration date September 10, 2009Last seen - Sep 10, 2009 01:15AM
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.
Ward- Nov 6, 2009 02:36PM
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
Answer
+0
moins plus
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

Prem Kumar- Apr 24, 2010 01:10AM
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.
Shukree- Sep 1, 2010 09:10PM
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 12:05AM
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 04:22PM
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 04:26PM
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
Answer
+0
moins plus
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

This document entitled « Excel VBA to filter rows containing same word » from Kioskea (en.kioskea.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.

Not a member yet?

sign-up, it takes less than a minute and it's free!

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.