Help With Major Excel Problem

Closed
nilnaij Posts 3 Registration date Monday October 21, 2013 Status Member Last seen October 23, 2013 - Oct 21, 2013 at 04:20 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Oct 29, 2013 at 11:56 AM
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
Related:

5 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Oct 22, 2013 at 10:29 AM
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
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Oct 21, 2013 at 12:09 PM
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
nilnaij Posts 3 Registration date Monday October 21, 2013 Status Member Last seen October 23, 2013
Oct 21, 2013 at 08:12 PM
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
nilnaij Posts 3 Registration date Monday October 21, 2013 Status Member Last seen October 23, 2013
Oct 23, 2013 at 03:53 AM
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!

Didn't find the answer you are looking for?

Ask a question
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Oct 29, 2013 at 11:56 AM
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