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]

I just really cannot wrap my head around a good what to do it. I have to check 4 different columns could also make the formula very long and messy.
If your data was structured differently, it might be a little easier, where you only had one column for Shareholder number and Shareholder amount, i.e.

if instead of having the data structured like this:

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​

it was structured in a more normalized function like this:

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

it might be easier to work with, as then you only have one column with "Shareholder Name" and "Shareholding %" to work with, though I still cannot wrap my head around exactly how to do it.