Previous Topic Next Topic

Excel VBA to filter rows containing same word

Closed
kelee Posts 4 Registration date Saturday September 5, 2009 Status Member Last seen September 10, 2009 - Sep 6, 2009 at 09:26 AM
 witsend - Oct 30, 2011 at 09:30 AM
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!
Related:

3 responses

Answer 1 / 3
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Sep 7, 2009 at 06:00 AM
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
kelee Posts 4 Registration date Saturday September 5, 2009 Status Member Last seen September 10, 2009
Sep 10, 2009 at 01:15 AM
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
Ward > kelee Posts 4 Registration date Saturday September 5, 2009 Status Member Last seen September 10, 2009
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
0
Answer 2 / 3
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Sep 6, 2009 at 08:34 PM
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
kelee
Sep 6, 2009 at 10:41 PM
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
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
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
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.
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...
0
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
0
Answer 3 / 3
witsend
Oct 30, 2011 at 09:30 AM
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

Similar discussions

How to convert the tamil pdf file to tamil word file
varathalakshmi -
karthik -

12 responses
How to convert the Tamil Word doc to Tamil PDF?
Singh -
Peter -

3 responses
Convert Tamil PDF document to Word
raje -
amc_88 -

2 responses