Join tables by text that contains

[FraM]

Dear Community, 

Aiming to calculate share of investors types (based on all active and former investors) for each company. As a first step for this, I would like to merge the two tables below. 

Table A: Companies with investor names

Company name	Active investor name	Former investor name
Company1	Investor1,Investor5	Investor100,Investor2
Company2	Investor1, Investor2...Investor20	Investor1, Investor2...Investor20
....
Company5000	Investor51	Investor3

Table B: Investors with investor types 

Investor name	Investor type
Investor1	Venture Capital
Investor2	Private Equity
....	...
Investor1000	State

The manual way would be to 1) Split text within "active investor name" and "former investor name" columns to several columns by , 2) Join each column via investor name from the second table. But this way I have to perform tons of joins separately as I will end up with hundreds of new columns that need to be joined with second table to get "investor type" - so I was hoping there might be an easier way? 

Output table should look like this. If you think there is an easier way to calculate the share of investor types per company, I'm also open to alternative approaches! 

Company name	Active investor name 1	Active investor name 1_type	Active investor name 2	Active investor name 2_type	Former investor name 1	Former investor name 1_type	Former investor name 2	Former investor name 2_type
Company1	Investor1	Venture Capital	Investor5	xxx	Investor100	xxx	Investor2	Private Equity

Many thanks in advance! 

[Emmanuel_G]

@FraM 

Find in attachement the way of doing this. I did a test with first line of companies and the first two rows of investors.

Let me know if there is any issue and please, do not hesitate to mark this answer as solution if it helped. 🙂

[FraM]

@Emmanuel_G 

Many thanks for your response! !The part of splitting the investor names to rows and transposing back works well.

However the join for the investor type only works for the specific investor selected via left join (e.g. "former investor 2" in your example). What I would need is a dynamic join to get a new field with the investor type for each active and former investor (some companies have up to 100 active or former investors).

Specifically for this example this would mean:

- If I add "Investor5" (which is the 2nd active investor of company1) with type "impact investor" to table B, I would automatically get a column with "active investor name2_ type" filled with "impact investor" in my output table.

- If I add "Investor100" (which is the 1st former investor of company1) with type "state investor" to table B, I automatically get a column with "former investor name1_type" filled with "state investor" in my output table

Do you have a suggestion how to build such a "dynamic join"? 

[Christina_H · Accepted solution]

@FraM What do you want the output to look like?  You can join while the data is vertical to get all investor types at once, then you just need to rearrange the output back to how you want it.

(Missing investor types are due to the test data being incomplete)

Edit: I've updated my workflow to put everything out sideways, which seems like what you need, see attached.