0

I have a spreadsheet exported from my database that contains Roles and Account IDs. There are 4-5 role names per account ID. I need a formula to display any accounts that do not have a role of RBD. Can anyone help?

Example:

Account ID   Account Name       Team        Role
1            123 AG SERVICE     National    MANG
1            123 SERVICE        National    CAP
1            123 AG SERVICE     National    RGL
1            123 AG SERVICE     National    CS
1            123 AG SERVICE     National    XSM
1            123 AG SERVICE     National    RBD
1            123 AG SERVICE     National    Q4
2            NORTHEAST INC      National    MANG
2            NORTHEAST INC      National    CAP
2            NORTHEAST INC      National    RGL
2            NORTHEAST INC      National    CS
2            NORTHEAST INC      National    XSM

I would want the query to display 2 as the account number that does not have the role of RBD.

Any help is appreciated!

  • 1
    Can you include a screenshot of the sheet so week can see how the columns are arranged? – P Fitz Jul 31 '14 at 15:52
  • Just what I was about to ask. I can't seem to be able to map the rows to the columns, e.g. the last lines apparently don't have an ID? Or "Account ID" is one column by itself. – Valmiky Arquissandas Jul 31 '14 at 15:54
0

SumProduct is your friend here. SumProduct allows you to carry out multiple tests on a range of data, row by row.

On a separate tab, list your distinct account IDs in Column A. In Column B we'll get a count of all Rows for each AccountID regardless of with a countif formula... like =countif(Sheet1!A:A, Sheet2!A1) assuming that Sheet1 has your data, and we are working on Sheet2.

In column C we'll deploy the sumproduct. We will want to return the count of rows for each accountID where the Role is not equal to RBD. This will look like =SUMPRODUCT((A1=Sheet1!$A$1:$A$13)*(Sheet1!$D$1:$D$13<>"RBD")*1). What this is doing is testing each one of the conditions in parantheses for true/false for each row in the range. Then it's adding up all the rows where both conditions were true. Our two conditions here are that Column A in Sheet1 has the AccountID we are interested in and that Column D in Sheet1 does not contain RBD.

In Column D of your second tab subtract Columns C from Column B: =B1-C1. If this is not 0, then the accountID is missing RBD.

This could all be written into a single formula instead of spreading it across columns B, C, and D like =if(countif(Sheet1!A:A, Sheet2!A1)-SUMPRODUCT((A1=Sheet1!$A$1:$A$13)*(Sheet1!$D$1:$D$13<>"RBD")*1) = 0, "", "Missing RBD!") if you are into the whole brevity thing.

You might, in the end, just be better off doing this with SQL on your database. Anytime you use sumproduct (or have a need for it) you are better off with SQL since it's more of a database type function. Something like

SELECT distinct_account_ids.Account_ID FROM (SELECT DISTINCT ACCOUNT_ID FROM <table>) as distinct_account_ids LEFT OUTER JOIN (SELECT Account_ID FROM <table> WHERE ROLES = 'RBD' GROUP BY Account_ID ) as ids_with_rbd ON distinct_account_ids.Account_id = ids_with_rbd.Account_id WHERE ids_with_rbd.Account_id is null;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.