Hello,
I have a data set which includes a data output from an external source (Bloomberg) where a series of advisors are listed out in a single cell and are also separated by "#N/A N/A" delimiters. Please see column R on the "Loans" tab.
You will note that the source data includes advisor type in CAPS. These include:
AGNT Agent(s)
BOOK Book Runner(s)
CLAR Co-Lead Arranger(s)
DOC Documentation Agent(s)
FULLLE LNDR Lender(s)
JOBO Joint-Bookrunners
LDAR Lead Arranger(s)
LGAB Legal Advisor(s) to Borrower
LGAL Legal Advisor(s) to Lender
LNDR Lender(s)
MAAR Mandated Arranger(s)
PART Participant(s)
SPNR Private Equity Sponsor(s)
SYND Syndication Agent(s)
I am looking to create a series of columns that summarize the advisor information available by target company. For example, I am looking for Columns to read 3 deep such as: AGNT 1, AGNT 2, AGNT 3, BOOK 1, BOOK 2, BOOK 3, etc as shown in the "End Goal" tab).
While I have used Excel to break the text to columns (as shown in tabs "Step 3" and "Step 4"), I cannot successfully implement via Join/Union functions.
Can anyone kindly provide some assistance with this task?
THANK YOU for your consideration as well - happy to elaborate as needed.
Regards,
Bob
