• 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.

wanted to seperate names by vba code

Piyush_Sanghavi

New Member
Hi,

I have an excel file in which column C (Assigned Engineer) having multiple names in multiple format. In this you may see TD: Voice: etc. I want to seperate the names in this column with the help of macros. Please assist.
 

Attachments

  • abcd.xlsx
    43.2 KB · Views: 23
Hi Piyush,

As there is no consistent format in words that needs to be deleted or retained. I suggest you two methods:

1. Case where you have exhaustive list of expected names. And use VBA macro to do string compare etc. Again this may not be right approach, I would on trial and error basis and tweak it accordingly.

2. Make a list of keys words that needs to be deleted like voice, data, security, TD:, Vx, N/a, Transformation etc. And use the VBA to run thru a loop to find and replace each of the listed words from your column C. You may need to keep adding words to the list as and when you see any new.

both the methods, may need to be revisited frequently to tweak to your needs. i do not think there would be any one time fix solution for the given situation.

Regards,
Prasad DN
 

Hi,

better for understand the need is to attach a workbook
with a source sheet and a desired result sheet …
 
Hi,

Thanks for the reply. I wanted the count of Engineer who is working on the request which is on column A. As it is possible1 or more engineer is working on one single PGSR number. It should count against the Engineer.

Thanks.
 
Oops! I click post reply before completing my view. the solution would help in fetching one engineer at a time; if your cell has more than one engineer the given formula may not work.

Regards,
Prasad DN
 
Hi,

Thanks for the reply Prasad. As you stated, I wanted to seperate multiple engineer name. The link you have posted can only filter 1 name at a time. It won't find multiple names.
 
Hi Piyush,

Attached is the file with VBA code that will fetch all the engineers worked on tasks listed in Col A.
1. You need to place all your engineers names in ColA of Sheet named "Names"
2. List all the tasks in ColA of "With VBA" sheet.
3. Press Ctrl+Shift+F to run the macro.

Also, I have made a non VBA (formula) based output in sheet without VBA. But it looks very long formula and is limited to 3 names in task. Some Excel Ninja here can rewrite the formula to make it more simple and not jus restricted to 3 names. in case you feel any of the task may have more than 3 names than use the VBA solution.

the VBA solution will check with no such limitation. It will find in given task if any of the listed names (engineers) appears and tracks them against it. As it needs to check all the engineers name for each task it may be slightly slow.

until you get a better solution from other ninjas you can use this for time being.

Regards,
Prasad DN.
 
Back
Top