Help With Major Excel Problem Closed

Question

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

TrowaD · Answer

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 · Answer

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

TrowaD · Answer

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

nilnaij · Answer

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!

TrowaD · Answer

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 IntegerlRow = Range("C" & Rows.Count).End(xlUp).RowFor 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 IfNext cellApplication.CutCopyMode = FalseEnd Sub

4)
Now you can delete columns B and C.

Best regards,
Trowa

Help With Major Excel Problem

5 responses

Similar discussions

Subscribe To Our Newsletter!