Another Step on INDEX/Aggregate

sdm100

New Member
Joined
Jun 15, 2013
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • Screenshot 2021-02-20 201127.jpg
    Screenshot 2021-02-20 201127.jpg
    65.4 KB · Views: 6

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,974
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 15, 2013
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,974
Office Version
  1. 365
Platform
  1. Windows
Thanks for that, how about
+Fluff 1.xlsm
ABCDEFGHIJK
1
2
3
4
5Asset 1Asset 2Asset 3Asset 4Asset 5Asset 2
6Company 160.00%Company 745.10%
7Company 2Company 827.90%
8Company 3Company 1919.42%
9Company 420.00%28.00%Company 207.50%
10Company 520.00%
11Company 6
12Company 745.10%67.00%
13Company 827.90%5.00%
14Company 9
15Company 10
16Company 11
17Company 12
18Company 13
19Company 14
20Company 15
21Company 16
22Company 17
23Company 18
24Company 1919.42%
25Company 207.50%
26Company 21
27
28
Data
Cell Formulas
RangeFormula
J6:J9J6=FILTER(B6:B26,FILTER(C6:G26,C5:G5=J5)>0)
K6:K9K6=FILTER(FILTER(C6:G26,C5:G5=J5),FILTER(C6:G26,C5:G5=J5)>0)
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
J5List=$C$5:$G$5
 

sdm100

New Member
Joined
Jun 15, 2013
Messages
13
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,974
Office Version
  1. 365
Platform
  1. Windows
Where in your sheet does it show the owner?
 

sdm100

New Member
Joined
Jun 15, 2013
Messages
13
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,974
Office Version
  1. 365
Platform
  1. Windows
Forgot you wanted the data sorted, so that's done, but a formula cannot see colour (or any type of format)
+Fluff 1.xlsm
ABCDEFGHIJK
1
2
3
4
5Asset 1Asset 2Asset 3Asset 4Asset 5Asset 3
6Company 160.00%Company 767.00%
7Company 2Company 428.00%
8Company 3Company 85.00%
9Company 420.00%28.00%
10Company 520.00%
11Company 6
12Company 745.10%67.00%
13Company 827.90%5.00%
14Company 9
15Company 10
16Company 11
17Company 12
18Company 13
19Company 14
20Company 15
21Company 16
22Company 17
23Company 18
24Company 1919.42%
25Company 207.50%
26Company 21
Data
Cell Formulas
RangeFormula
J6:K8J6=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
CellAllowCriteria
J5List=$C$5:$G$5
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,974
Office Version
  1. 365
Platform
  1. Windows
Can one company be the owner of more than one asset?
 

sdm100

New Member
Joined
Jun 15, 2013
Messages
13
Office Version
  1. 365
Platform
  1. Windows
That is amazing, thank you so much for your help!
 

Forum statistics

Threads
1,144,339
Messages
5,723,800
Members
422,517
Latest member
VisioExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top