Help With Major Excel Problem

[Closed]
Report
Posts
3
Registration date
Monday October 21, 2013
Status
Member
Last seen
October 23, 2013
-
Posts
2807
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 21, 2021
-
Please help with the problem if you can
I have a single column of data numbering in 650k

The Problem I have here is within this column
I have companies with almost similar names which I want to track and clear
E.g
Cell A1- ABC LTD
Cell A2 - ABC PTE LTD
Cell A3 - ABC PTE
Cell A4 - Beijing Ltd
Cell A5 - Beijing Pte Ltd
Cell A6- Beijing Pte

I just want to track anyone of these names which are similar example a function / macro / vb that can tell me which 3 cells are similar then I can make the manual editing myself or if theres a automated way then please tell me

Thank you
Much Appreciated

5 replies

Posts
2807
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 21, 2021
482
Hi Nilnaij,

So Happy = Sad ?
I thought you wanted to single out the first word.

Turn:
ABC LTD
ABC PTE LTD
ABC PTE
Beijing Ltd
Beijing Pte Ltd
Beijing Pte
Happy Ltd
Sad Pte Ltd

Into:
ABC
ABC
ABC
Beijing
Beijing
Beijing
Happy
Sad

Could you give result as you would like to see it?

Best regards,
Trowa
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
2807
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 21, 2021
482
Hi Nilnaij,

Is it an idea to use find/replace. First replace (Ctrl+H) ltd with nothing and then pte with nothing. You could use an additional column if you don't like to mess with the original. Just keep in mind to SELECT YOUR COLUMN FIRST.

Best regards,
Trowa
Posts
3
Registration date
Monday October 21, 2013
Status
Member
Last seen
October 23, 2013

Hi Trowad

I used the top example as a minor reference within my column I have other accurate names also which contains LTD and PTE

A7: Happy Ltd
A8: Sad Pte Ltd <-- These 2 are accurate so if I were to utilize ur method I might affect these correct columns too

The method im actually looking for is something to single out those cells that are similar without affecting those cells without any similarities




Thanks for the tip though! :D
Posts
3
Registration date
Monday October 21, 2013
Status
Member
Last seen
October 23, 2013

Hi Trowa, Thanks for your earlier reply

I wana try to standardize

ABC PTE LTD
ABC PTE LTD
ABC PTE LTD
BEIJING PTE LTD
BEIJING PTE LTD
BEIJING PTE LTD
Happy Ltd
Sad Pte Ltd

I gave up on eyeballing for the similarities to change because I have 650k of Row, if there was a way I could identify all these cells at the same time and group them together so I can make the changes it would be so much easier. Like looking around with a partial cell match or smth.

Thanks man!
Posts
2807
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 21, 2021
482
Hi Nilnaij,

Try this and see if it works (it works with sample data):


1)
Column A: Here are the company names.
Column B: This is a formula: =Len(A1). Drag the formula down to match column A.
Column C: Copy/paste company names here. Select column --> find/replace (Ctrl+H):
PTE with nothing
LTD with nothing
Space with nothing

2)
Select column A, B and C. Sort data:
Column C --> ascending
Column B --> descending

3)
Run the following code:
Sub RunMe()
Dim lRow As Integer
lRow = Range("C" & Rows.Count).End(xlUp).Row
For Each cell In Range("C1:C" & lRow)
cell.Offset(0, -2).Copy
If cell.Value = cell.Offset(1, 0).Value Then
cell.Offset(1, -2).PasteSpecial
End If
Next cell
Application.CutCopyMode = False
End Sub

4)
Now you can delete columns B and C.

Best regards,
Trowa