Need formula to tag "Unmanaged" or blank field by Org ID

Hi.  Please see attached file.  

I need a formula that looks at every Org ID and identifies only those with a lead whose Qualification Outcome is either labeled "Unmanaged" or is left blank.

Thank you in advance for any help given!

Gilbert
Example-EE.xlsx
glabossiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello glabossi,

"identifies" how? Can you give a few examples of the expected outcome? Do you want the Org IDs to change color, or set a flag of some kind in a different column?

Also, where is the relation to the Lead? Isn't the qualifying criterion simply the text in the Qualification Outcome?

cheers, teylyn
0
DonkeyOteCommented:
Why not use a Pivot Table ?

You could set up such that:

Qualification is a Report Filter -> select Unmanaged and blank

Company Name is Row Label

If you want more info. - ie Leads etc then adjust as necessary.
0
FayazCommented:
Give an example what you expect.  
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

darkchild173Commented:
What you are looking for is the following formula pasted ina new column next to your existing columns (this particular formula is for row 3).
It will only put the values of Org ID based on your criteria, otherwise it leaves the cell blank. You can then sort the whole sheet according to your needs.

(I also attached an updated example)

=IF(OR(D3="Unmanaged", D3="")=TRUE,IF(C3>0,A3,""),"")

Cheers!


Example-EE-1-.xlsx
0
glabossiAuthor Commented:
Hi everyone - sorry for the confusion.

What I need is for every Org ID, a flag (could be a 1) only if they have "Unmanaged" or blank in the field.

Then I will create a pivot to filter those in or out.

I hope you can help!!!
0
darkchild173Commented:
Modified according to the new info:
=IF(OR(D3="Unmanaged", D3="")=TRUE,IF(C3>0,1,""),"")

Hope this works for you.
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
In this case, in E1

=IF(OR(D1="Unmanaged", D1="")=TRUE,1,"")

copy down

cheers, teylyn
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Or, even shorter

=IF(OR(D1="Unmanaged", D1=""),1,"")
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
darkchild173Commented:
Oh, and right now it also excludes those rows where column C is blank (as i gathered it should from your description). If that's not ok, use this:

=IF(OR(D3="Unmanaged", D3="")=TRUE,1,"")

Cheers.
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
darkchild,

the =TRUE is not required, since the OR() already evaluates to either TRUE or FALSE.

cheers, teylyn
0
DonkeyOteCommented:
>Then I will create a pivot to filter those in or out.

Can you not just use the filed as a Report Filter as already outlined and filter on that basis ?

Regards the formula:

=OR(D3={"Unmanaged",""})+0
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.