Kind of concatenation

[Kel12]

I need to have this kind of concatenation

FROM:
DIRECTOR John
DIRECTOR Mary
MANAGER Robert
MANAGER Rashed

WANTED RESULT:
DIRECTOR John, Mary
MANAGER Robert, Rashed

Can you suggest formulas to do so ?

[oeldere]

You get better result on your question if you add a small excel file, without confidential information.

Please also add the expected results manualy in your file.

[Jdevil]

Is the role title always only one word? Always in Caps? Can there be more than 2 names? Posting a more complete file would indeed be more helpful

[Kel12]

OK Thanks for the hint
Concatenate.png

[Kel12]

Still trying attach an excel or image but not clear as how to do it.

[Kel12]

The role title could be several words and not always in Caps. But seems that it should not matter.
There can be only 1 name.

Please see image attached. THX

[oeldere]

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[Jdevil]

Can the name of the person have a space in it? Are you willing to use VBA, or helper column and pivot table?