Excel macro for returning values based on adjacent cells and further calculations

[MiteshG]

Hi Members,

I have excel data in following format. (example)

Entity​	Shareholder 1​	Shareholding %​	Shareholder 2​	Shareholding %​	Shareholder 3​	Shareholding %​
ABC​	XYZ ltd​	50​	STU ltd​	25​	PQR ltd​	25​
XYZ ltd​	DEF ltd​	75​	JKL ltd​	25​

Based on this data, I would like to calculate indirect shareholding % of DEF ltd in each entity. Like below -

Entity​	Shareholder 1​	Shareholding %​	Shareholder 2​	Shareholding %​	Shareholder 3​	Shareholding %​	DEF Ltd. Indirect Holding %​
ABC​	XYZ ltd​	50​	STU ltd​	25​	PQR ltd​	25​	37.5​
XYZ ltd​	DEF ltd​	75​	JKL ltd​	25​			75​

I have done this calculation manually but this is to be done for multiple rows. What is the best way to do this calculation through either macro or formula?

Thanks in advance for the help.

 

[Joe4]

If you explain exactly how the formula is calculated (in plain English, referring to your sample data), we can probably help you come up with the formula you need.

 

[MiteshG]

Sure @Joe4. Thanks for your response. I have attached the mini sheet here.

Book3
	E
6
Sheet1

Essentially, DEF ltd could be a direct Shareholder 1, 2 or 3 for any entity and it can also be a indirect shareholder (as shown in the 1st data row). My goal is to calculate the shareholding % of DEF for each entity

 

[Joe4]

You need to select more than one cell when copying/pasting the mini-sheet.
Select all the cells you want to copy first.

 

[MiteshG]

Sure @Joe4. Thanks for your response. I have attached the mini sheet here.

Book3
	E
6
Sheet1

Essentially, DEF ltd could be a direct Shareholder 1, 2 or 3 for any entity and it can also be a indirect shareholder (as shown in the 1st data row). My goal is to calculate the shareholding % of DEF for each entity

Correct sheet

Entity	Shareholder 1	Shareholding %	Shareholder 2	Shareholding %	Shareholder 3	Shareholding %	DEF Ltd. Indirect Holding %
ABC	XYZ ltd	50	STU ltd	25	PQR ltd	25	37.5
XYZ ltd	DEF ltd	75	JKL ltd	25			75

 

[MiteshG]

You need to select more than one cell when copying/pasting the mini-sheet.
Select all the cells you want to copy first.

Corrected and attached updated sheet

 

[Joe4]

Sure @Joe4. Thanks for your response. I have attached the mini sheet here.

Book3
	E
6
Sheet1

Essentially, DEF ltd could be a direct Shareholder 1, 2 or 3 for any entity and it can also be a indirect shareholder (as shown in the 1st data row). My goal is to calculate the shareholding % of DEF for each entity

Sorry, it still is not quite clear to me.
Remember, while you are familiar with the problem and what you want to do with it, we are not. All we know is what you elect to share with us here.
So pretend that you are doing a presentation to a group of people who know nothing about what you are trying to do.

Please walk us through an actual detailed example, quoting numbers, and show us exactly how you arrive at those numbers.

 

[MiteshG]

Sorry, it still is not quite clear to me.
Remember, while you are familiar with the problem and what you want to do with it, we are not. All we know is what you elect to share with us here.
So pretend that you are doing a presentation to a group of people who know nothing about what you are trying to do.

Please walk us through an actual detailed example, quoting numbers, and show us exactly how you arrive at those numbers.

To explain in detail -

Problem Statement - To calculate shareholding % of a company in an entity.

Example 1 -

Entity​	Shareholder 1​	Shareholding %​	Shareholder 2​	Shareholding %​	Shareholder 3​	Shareholding %​	DEF Ltd. Indirect Holding %​
XYZ ltd​	DEF ltd​	75​	JKL ltd​	25​

In the above example, DEF ltd is a direct shareholder of XYZ and shareholding % as 75%. So, my answer in this case would be 75%.

Example 2 -

Entity​	Shareholder 1​	Shareholding %​	Shareholder 2​	Shareholding %​	Shareholder 3​	Shareholding %​	DEF Ltd. Indirect Holding %​
ABC​	XYZ ltd​	50​	STU ltd​	25​	PQR ltd​	25​

In the above example, DEF ltd is not a direct shareholder of ABC. However, XYZ is a shareholder of ABC at 50% and as we saw in example 1, DEF is shareholder of XYZ at 75%. So, DEF is indirectly holding ABC. Now, to understand what's the % of indirect holding, I have considered (50*75)/100 = 37.5. So, DEF is indirectly holding 37.5% of ABC.

As seen in above examples, DEF can be a direct or indirect shareholder in each entity and I need to calculate the % holding (either direct or indirect)

 

[Joe4]

Oh wow, this looks like it could get really messy/complicated, not just because of the INDIRECT nature, but also because you have multiple columns on each row for Shareholders (Shareholder 1, Shareholder 2, Shareholder 3). I am not sure if you might really have more than 3 Shareholders for a row, and just simplified it here for demonstration purchases.

And your second row shows an Indirect relationship, but might you also have some indirect relationship 2 or more levels removed?
For example, a 3rd row in which maybe you have entity MMM with has ABC as a shareholder.
So since MMM has ABC as a Shareholder
and ABC has XYZ as a Shareholder;
MMM has an indirect holding of Def Ltd.

Now I can see why you are doing this manually. I cannot think of a really good way to do it, especially the way that the data is structured.

 

[MiteshG]

Oh wow, this looks like it could get really messy/complicated, not just because of the INDIRECT nature, but also because you have multiple columns on each row for Shareholders (Shareholder 1, Shareholder 2, Shareholder 3). I am not sure if you might really have more than 3 Shareholders for a row, and just simplified it here for demonstration purchases.

And your second row shows an Indirect relationship, but might you also have some indirect relationship 2 or more levels removed?
For example, a 3rd row in which maybe you have entity MMM with has ABC as a shareholder.
So since MMM has ABC as a Shareholder
and ABC has XYZ as a Shareholder;
MMM has an indirect holding of Def Ltd.

Now I can see why you are doing this manually. I cannot think of a really good way to do it, especially the way that the data is structured.

Thanks @Joe4.

To answer the 1st question - I have values till Shareholder 4. (1, 2, 3, 4 in total)
Regarding the indirect relationship, it exists at 1 level only. So, I was hoping there could be a way to achieve this through some formula or macro as I have 1000 data rows to calculate