• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

One result from multiple rows

B

BirdyBee

New Member
Hi Ninjas and other Excel gurus out there!

I am pretty basic Excel user and a fan of this website! I am responsible for rolling out a quality assurance scheme across a number of organisations (column A and B). These organisations operate multiple services (column D and E) in different areas or that offer different types of services.

I have assigned each service an inclusion status (column F) based on a number of criteria to reflect the extent to which the service will be included in the quality assurance scheme.

What I want to do now is assign each organisation a single inclusion status that is based on all its services’ inclusion statuses. I need a formula or macro or something clever that delivers this in column C but I am struggling to come up with it!

I want the formula to look at all of the organisations’ services and, based on the hierarchy below, assign the correct inclusion status in column C. For now I have manually typed each organisation’s inclusion status into column C to show the result I want delivered but of course I need a formula to automate this result!

The hierarchy is:

1. In-Scope
2. TBA
3. Other Standards
4. Self-Assessable
5. Not Required.

For example, if In-Scope appears for any of the organisation’s services, the organisation's inclusion status will be In-Scope. If there are no In-Scope services but there are any TBA services, then the organisation’s status will be TBA and so on moving down the hierarchy.

If you can help I would be eternally grateful!
 

Attachments

  • Organisation Inclusion Status.xls
    18 KB · Views: 9
Thank you both for responding to my query! You are geniuses! I am hoping I can now donate to you through the donation page as a thank you.
 
Thank you for your generosity! I do like the idea of the donation page very much as it means that you are not just asking but giving in return. The Excel community is an interesting one!
 
Hi I was wondering if Somendra or somebody else is out there who can help me. I posted the above problem a while ago and I have tried the solution suggested by Somendra on 19 October 2014 in my spreadsheet but it is not working properly.

I have attached a copy of my de-identifed spreadsheet. Column G shows the result using the formula supplied by Somendra previously - it works but not properly - I must have done something wrong! In column H I have inserted the correct answer that the formula should return. The original problem is well described above.

As always, I am in awe of your skills and generosity and would be grateful of any help you can provide.
 

Attachments

  • Organisation Inclusion SS.xlsx
    17.5 KB · Views: 3
Unfortunately I don't really understand how to use tables so I confess I tried Somendra's solution. Maybe I should look up a tutorial on tables....
 
Hi ,

The only problem in your file is that you have not entered the formula as an array formula ; an array formula needs to be entered using CTRL SHIFT ENTER ; you need to press these 3 keys together ; generally this is done by keeping 2 of the 3 keys pressed , and then pressing the third key.

Once you do this , the formulae are returning the correct results.

Narayan
 

Attachments

  • Organisation Inclusion SS.xlsx
    18.5 KB · Views: 5
Narayan very thanks for taking the time to reply - to both solutions! Your help and patience are very much appreciated.
 
You must log in or register to reply here.
Back
Top