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: 9

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,089
Office Version
  1. 365
Platform
  1. Windows
My pleasure.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,863
Office Version
  1. 365
Platform
  1. Windows
Just a marginally different approach but really wondering if something like this conditional formatting would help in checking the managing owner?

21 02 21.xlsm
ABCDEFGHIJK
1
2
3
4
5CompanyAsset 1Asset 2Asset 3Asset 4Asset 5CompanyAsset 3
6Company 10.6Company 70.67
7Company 2Company 40.28
8Company 3Company 80.05
9Company 40.20.28
10Company 50.2
11Company 6
12Company 70.4510.67
13Company 80.2790.05
14Company 9
15Company 10
16Company 11
17Company 12
18Company 13
19Company 14
20Company 15
21Company 16
22Company 17
23Company 18
24Company 190.1942
25Company 200.075
26Company 21
Filter n Sort
Cell Formulas
RangeFormula
J6:K8J6=LET(cols,FILTER(B6:G26,COUNTIF(J5:K5,B5:G5)),SORT(FILTER(cols,INDEX(cols,0,2)>0),2,-1))
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B6:B26Expression=COUNTIF($J$6#,B6)textNO
Cells with Data Validation
CellAllowCriteria
K5List=$C$5:$G$5



If the lists are large then you could put an AutoFilter on column B and Filter by colour for the highlighted rows to make checking more compact.

21 02 21.xlsm
BCDEFG
5CompanyAsset 1Asset 2Asset 3Asset 4Asset 5
9Company 40.20.28
12Company 70.4510.67
13Company 80.2790.05
Filter n Sort
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B6:B26Expression=COUNTIF($J$6#,B6)textNO
 

Forum statistics

Threads
1,148,427
Messages
5,746,618
Members
424,033
Latest member
al1en

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