• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to sort related string in a column of a worksheet.

J

Jagdev Singh

Active Member
Hello Everyone

Happy to be part of Chandoo forum.

I am facing an issue while creating a Macro in VBA. We have a sheet which consist entries in 1000's. The entries in a column is combination of correct and partially correct entries.

Ex -

ABC company LTD
Company ABC LTD

I can't use Excel function to sort such entries.

Is there a way to create Macro which will filter such result in a column and sort the related data one after the other. Like the way it is appearing in the example above.

Thanks in advance.

Regards,
Jagdev
 
Jagdev

Firstly, Welcome to the Chandoo.org Forums

Unfortunately as far as Excel is concerned these two entries are totally independent

You will need to fix these first

one of the easiest ways to identify these I have found is convert the data to a table
Select a cell in the data
Goto Insert, Table
Then you can use the dropdown on the Company field and search for similar entries
Select them and change them all at once
 
Hui said:
Jagdev

Firstly, Welcome to the Chandoo.org Forums

Unfortunately as far as Excel is concerned these two entries are totally independent

You will need to fix these first

one of the easiest ways to identify these I have found is convert the data to a table
Select a cell in the data
Goto Insert, Table
Then you can use the dropdown on the Company field and search for similar entries
Select them and change them all at once
Click to expand...

Hi Hui

Thanks for getting back to me on the above query. The issue is that we have 1000's of entries in this passion and changing them with the above criteria is bit hectic and consume huge amount of time/manual resouce.

Is it possible to create a macro and put condition in this column, like in the above example if it find the word "ABC" in the column, move both the correct and partially correct entry with its adjustant data to other/adjucent sheet and that to in sequencial order.

Please let me know if it is feasible. Your advice is highly appreciated.

Regards,
Jagdev
 
Last edited:
Hui said:
Can you post a sample file ?
Click to expand...

Hi Hui

Please find the sample file attached with the past.

In Column A you will find the raw data and in Column D you will find the correct result. Is there a way to create a Macro which on a click extract the result in the way currently it is in Column D.

Looking for your possitive reply.

Regards,
Jagdev
 

Attachments

  • Sample.xlsx
    10.7 KB · Views: 4
Hi Jaggi ,

My opinion is that as a first step we should eliminate the common words ; some of these would be :

Company , Co. , Co , Limited , Ltd. , Ltd , Corporation , Corp. , Corp

You can , since you have your working data , take a glance at what else can be removed ; once this is done , then a comparison will be more meaningful , since ABC Company will match with ABC Co. Ltd.

You need to decide whether removal of such terms will result in false matches.

Narayan
 
Hi Narayan,

Thanks for your view on the thread..I am fine with the elimination of common word. My concerned here is to sort the related entry in a order..I am fine if you set your trigger on the company name like "ABC" in the above example..I presume you have looked the sample file attached in the above thread.

I would like to inform you that in the data sheet which I need to filter contains 2 entries of same entity "ABC Company". The issue is in the order they are presented in the sheet.

Regards,
Jaggi
 
Hi Jaggi ,

I had earlier seen your workbook , and I was not sure about anything since the data set was so small.

You have placed PQR Company before PQR , XYZ Company Limited before XYZ China , IJK Music is before IJK Corporation , but EFC Consult is before EFC Group.

What is the rule behind this ordering ?

Narayan
 
Hi Narayan

For security reason I replace the actual name with alphebates "ABCD" the rest of the name is fix. This is just to give you the idea what exactly the issue is.

About the ordering of data:

"You have placed PQR Company before PQR , XYZ Company Limited before XYZ China , IJK Music is before IJK Corporation , but EFC Consult is before EFC Group."

I get the raw data in this way. My task is to find these partially similar data in a column and make the changes in the unsimilar data accordingly.

The data is not in sequency like
PQR
PQR Company
XYZ
XYZ Company etc....

I am looking for something which help me to get the data in the above order.

Please let me know if you are still unclear with the requirement.

Regards,
Jaggi
 
Hi Jaggi ,

I think now you should see your file !

I can understand that you receive the data in a randomly ordered manner.

But in column D , you have listed the same data with the header Correct Order of Data ; I am referring to the items in this column. Why have you listed the items the way I mentioned in my earlier post ?

Narayan
 
Hi Narayan

The Column D is the way I want the data from the macro. The column A is an example of RAW data and Column D is the result/outcome we want.

Regards,
Jaggi
 
Hi Jaggi ,

I think we are wasting our time !

1. I know your RAW data is in column A ; can we take this for granted and move on ?

2. I know the list in column D is the way you want the list in column A reordered ; can we take this also for granted ?

Please tell me why you have listed the items in column D the way they are ; they do not appear to be in ascending or descending alphabetical order. Have you followed any custom list in ordering the data ?

Narayan
 
Hi Narayan

Sorry again for the confusion...

I have not followed any custom or ordering the data on column D. I just rearrange them manually to make it understandable to the experts. I just want the output to be in ordered on a click for entries which are in 1000s.

Regards,
Jagdev
 
Hi Jaggi ,

In that case , can we start with what I had posted earlier ? That we remove a first set of common words , and sort the list alphabetically and see if that helps ; probably a majority of the names may be OK.

We can thereafter look at some exceptions and see how they can be taken care of.

If you can upload a sample file with at least a few hundred names , we can try.

Narayan
 
You must log in or register to reply here.
Back
Top