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!
Solved! Go to Solution.
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. 🙂
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"?
@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.