Matching Last Names in Two columns and pick up certain ones

Column “A” contains names of authors for papers from a number of organizations in the format
Last Name, (comma ) then First Name initials

Column “B” contains the names of authors from a specific organization in the format, generally Last Name; (semicolon) then full first name. In some case, Last Name; (semicolon) first name initials.

All the names in column “B” are sub set from column “A”.
The Last Name is common between column “A” and “B”.

I am looking for a formula in Column “C” that produce the names in the format Last Name, (comma) first initials as in column A for organization X
The attached file provides sample of the massive data that I have.
Any help is greatly appreciated.

Dear Pjwhitfield

Many thanks for your reply.
Excuse my ignorance, this looks like a macro but I could not run it.

I copied it as is to "View Code" and run it. It then asked me to give it a name and I did but it did not do any thing in column "C"


What do I need to do?

All the best Taisir

I uploaded the correct file again with a single sheet

Dear Friends,

I am re-posting this request in the VBA section from the “Formula Section”, since I was not able to obtain the right formula to do the job.
In the attached file,
Column “A” contains names of authors of papers from a number of organizations in the format Last Name, (comma ) then First Name initials;

Column “B” contains the names of authors for a specific organization in the format, generally Last Name; (semicolon) then full first name. In some case, Last Name; (semicolon) first name initials.

All the names in column “B” are sub set from column “A”.
The Last Name is common between column “A” and “B”.

I am looking for a formula in Column “C” or a Macro (VBA) that produces the names in Column B in the format Last Name, (comma) first initials as in column "A" for organization X.

Basically, a formula or a VBA that examines last name in column “B”, then find its match in column “A” and then copy how its written in “A” to column “C”

The attached file provides sample of the massive data that I have.

Any help is greatly appreciated.
Thanks in advance

Dear All

Many thanks for Karedog for the the VBA that worked perfect. However, I have similar problem which I wonder whether the VBA can be adjusted or a new VBA is needed.

The attached sheet is a sample of the data that I have and illustrate the problem and the desired outcome.

Column “A” contains names of authors of papers from a number of organizations in the format Last Name, (comma ) then First Name initials;

Column “B” contains the affiliations of the same authors of column “A” but in the format Last Name, (comma) then full First Name. In some case, Last Name, (comma) Initials. Authors from the same institutions are separated by semicolon(.
The Last Name is common between column “A” and “B”.

Basically, I am looking for a VBA that examines Last Names in column “B” for each author, then find its match in column “A” and then reproduce column “B” in Column “C” with how its written in “A”.

The attached file provides sample of the massive data that I have.
Any help is greatly appreciated.
Thanks in advance

Dear Karedog

I really appreciate your help. I tested the VBA on another sample sheet and worked just fine. Magic

Will mark this thread as solved. Taisir

Dear Karedog again,

I am trying to run the VBA on the full file and it keeps stopping with Error.

Can I ask you please to test on the attached.

My apologies for keep coming back to you.
Many thanks in advance

Taisir

Many thanks Karedog.
I will use this macro heavily on hundred of files for different universities in the next few months, I really appreciate the great help.

I will keep you posted with any difficulty. Take care and all the best