Another Step on INDEX/Aggregate

[sdm100]

Hello,

I have a spreadsheet of Assets across C:CA and in Col B I have a list of companies that could own those assets. Many of the same companies own multiple assets in varying percentages. As you can see from the simplified example photo, there are lots of blank spaces and there is a litter of percentage numbers. Of course if you sum the percentages in any column, the total will always equal 100%.

I would like to use another tab to go into the Ownership tab and ignore the blank spaces so that when I select an asset on a dropdown or similar, when Asset 1 comes up, then the formula will go and grab the equity positions and fill them in the cells under the name ignoring all the blank spaces. It will never be the case that an asset will have more than 7 owners and I am fine with blank cells if there is less than 7 owners.

I am using Index/Aggregate where I am searching for any value above basically zero and dividing that by itself to tease out the TRUE references, but I am unable to find a way to do this dynamically - as you can see in the formula below, this is only searching Col C for Asset 1 and when I want to find the ownership in Asset 2 (for example, I am copy and pasting the formula and then doing a Find/Replace for the col ref).

Is there a simple way to search for the column heading e.g. Asset 2, then apply the relevant INDEX/AGGREGATE below without VBA? I have tried to use INDEX/MATCH but I don't think the syntax works.

I have attached a simplified example of the data set and the cluster in Bold on the right would be the end result and imagine that the heading in bold (Asset 2) could be dropped down to any asset name and the data would refresh underneath ignoring all the empty rows that are in the relevant columns.

=INDEX('Ownership'!$C$6:$C$60,AGGREGATE(15,3,(IF('Ownership'!$C$6:$C$60>0.001,TRUE,FALSE)/IF('Ownership'!$C$6:$C$60>0.001,TRUE,FALSE))*(ROW('Ownership'!$C$6:$C$60)-ROW('Ownership'!$C$5)),ROWS($A$1:A1)))

Apologies if this is an obvious one to solve but I am struggling to adjust the above formula to solve the issue.

P.S. Even more desirable would be if they were called into the simplified list in order of highest to lowest equity position and if there was a way to tag the managing owner of the Asset (which will always only be one company), which may not be the company with the biggest equity position. I was thinking that in my data entry I could mark the percentage interest of the managing partner in Green and if the formula could find a way to pull that in and maybe concatenate the Company name to for example "Company 1 Managing Owner" or tag it in some way (even just make it bold) But I don't know if that is possible.

Thanks,
S

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[sdm100]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thank you, all noted and I have updated to 365 and Windows as the platform

 

[Fluff]

Thanks for that, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2
3
4
5			Asset 1	Asset 2	Asset 3	Asset 4	Asset 5			Asset 2
6		Company 1	60.00%							Company 7	45.10%
7		Company 2								Company 8	27.90%
8		Company 3								Company 19	19.42%
9		Company 4	20.00%		28.00%					Company 20	7.50%
10		Company 5	20.00%
11		Company 6
12		Company 7		45.10%	67.00%
13		Company 8		27.90%	5.00%
14		Company 9
15		Company 10
16		Company 11
17		Company 12
18		Company 13
19		Company 14
20		Company 15
21		Company 16
22		Company 17
23		Company 18
24		Company 19		19.42%
25		Company 20		7.50%
26		Company 21
27
28
Data

Cell Formulas
Range	Formula
J6:J9	J6	=FILTER(B6:B26,FILTER(C6:G26,C5:G5=J5)>0)
K6:K9	K6	=FILTER(FILTER(C6:G26,C5:G5=J5),FILTER(C6:G26,C5:G5=J5)>0)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
J5	List	=$C$5:$G$5

 

[sdm100]

Thank you so much, I have never even seen the Filter function and that is so much neater than INDEX/Aggregate - this is great. Do you have any thoughts on how I could mark the managing owner? It will be one entity that is filtered per asset and unfortunately it isn't always the largest shareholder. So I will have to manually tag the information on my input sheet.

Do you think that VBA would work to look at the original addresses of the filtered cells and establish which cell is has been shaded to green ?

 

[Fluff]

Where in your sheet does it show the owner?

 

[sdm100]

Where in your sheet does it show the owner?

I think because Companies in Col B will be a mixture of managing owner and non-managing owner on different assets, I think the only thing I could do would be to tag the percentage relating to the managing owner.

So for example in the above if Company 4 was the managing owner of Asset 3 then I would tag 28.00%mgreen (or similar) manually to give Excel something to look up. I couldn't just tag Company 4 because it might not be the managing owner for its other equity interest in Asset 1.

I might be asking for the impossible, but once the filter has ran, I am wondering if we could track the addresses of the original cells that are in K6:K9 and if it finds the tag/colour/whatever then mark the relevant %age interest in K6:K9 accordingly or mark it in the next column as "Manager" or something.

I'd just like a visual way to show this as on my master sheet I am working with about 300 assets and having a quick check on which company ultimately controls/runs the asset would be an unbelievable time saver.

 

[Fluff]

Forgot you wanted the data sorted, so that's done, but a formula cannot see colour (or any type of format)

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2
3
4
5			Asset 1	Asset 2	Asset 3	Asset 4	Asset 5			Asset 3
6		Company 1	60.00%							Company 7	67.00%
7		Company 2								Company 4	28.00%
8		Company 3								Company 8	5.00%
9		Company 4	20.00%		28.00%
10		Company 5	20.00%
11		Company 6
12		Company 7		45.10%	67.00%
13		Company 8		27.90%	5.00%
14		Company 9
15		Company 10
16		Company 11
17		Company 12
18		Company 13
19		Company 14
20		Company 15
21		Company 16
22		Company 17
23		Company 18
24		Company 19		19.42%
25		Company 20		7.50%
26		Company 21
Data

Cell Formulas
Range	Formula
J6:K8	J6	=LET(Col,FILTER(C6:G26,C5:G5=J5),SORT(CHOOSE({1,2},FILTER(B6:B26,Col>0),FILTER(Col,Col>0)),2,-1))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
J5	List	=$C$5:$G$5

 

[Fluff]

Can one company be the owner of more than one asset?

 

[sdm100]

That is amazing, thank you so much for your help!