Identifying multiple values that meet a pre-determined criteria

[Sof23]

Hello everyone,

I managed to solve a problem that I posted here yesterday, thank you again for the people which helped.

Currently I am trying to solve the following issue - background: I have a list of asset types (numbered from 1->12 on columns D->O) and on the rows the number of each asset type (L2 shows that there are 34 items under asset type "9"). Column "C" has the total number of assets for each row. The ID (column A) is unique in the entire sheet, there are no duplicates. On column P I have calculated a simple formula that shows me, should an asset represent more than 50% of the total for each row, the number of the asset (P7 shows asset "3" because asset 3 represents more than 50% of the total assets).

Now to the problem - I am trying to identify, should this formula result in "None", what are the asset classes for a lower percentage than the one already specified (50%) AND if there are two relatively-close asset classes to list both of them together should the asset class represent more than 30% of the Total. In the attached worksheet, on row 2, the current solution lists it as "None" since none of the assets surpass 50% of total assets, however in reality I want it to show "10 & 2* because both of those asset classes surpass my 30% threshold.

Much appreciated!!

[PaulM100]

Delete 3rd condition if you do not want it:

Please Login or Register  to view this content.

[Sof23]

Thank you for replying, PaumM! I used your formula and it almost solved the entire problem. For the 3rd condition I deleted ""Above 30,under 50 - "" only since I need the row marked with the corresponding asset class as long as it is above 30%. In my actual datasheet, there is one inconsistency that I do not know how to solve: whenever two asset classes have the same amount, it will only list the later asset class twice. Example: Total= 6, asset 1 has 2 values, asset 2 has 1 value, asset 3 has 1 value, asset 4 has 2 values. It should say "asset 1 and asset 4" but currently it says "asset 4 and asset 4". Do you know how I may solve this?

[PaulM100]

I will have a go on it, but not sure if I will fix it cause working with returning headers is a pain in the ***

[PaulM100]

See if this works:

Please Login or Register  to view this content.

[Sof23]

See if this works:

Please Login or Register  to view this content.

It worked perfectly, thank you again! I am just starting out with excel and your approach to my initial first half that I solved with other formulas was really cool and made me learn a few extra formulas, so thank you for that as well. I will dissect the entire formula in steps until I understand it fully.