Results 1 to 8 of 8

Thread: Excel 2007, a formula to check multiple rows for a given customer name

  1. #1

    Excel 2007, a formula to check multiple rows for a given customer name



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    Column A = Customer Names (many duplicate entries, because of column B)
    Column B = Customer Location (all customers have multiple locations)
    Column C = "type1" or "type2"

    a customer might have "type1" and "type2" locations

    How do we make a formula, for column D, to state if the customer has both types?

    something like: "IF Customer Name Has "type1" AND "type2" then say "MIXED" else say "NOT MIXED"


    thanks

    data would look like:
    CustomerAB | 555 street | type1 | mixed
    CustomerAB | 500 street | type2 | mixed
    CustomerXZ | 111 street | type1 | not mixed
    CustomerXZ | 089 street | type1 | not mixed

  2. #2
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    177
    Articles
    0
    Excel Version
    2010
    For example:
    =IF(AND(COUNTIFS(A:A,A2,C:C,"type1")>0,COUNTIFS(A:A,A2,C:C,"type2")>0),"mixed","not mixed")
    Circumference of a circle = 2πrē



    ēthe circle's radius

  3. #3
    Quote Originally Posted by JoePublic View Post
    For example:
    =IF(AND(COUNTIFS(A:A,A2,C:C,"type1")>0,COUNTIFS(A:A,A2,C:C,"type2")>0),"mixed","not mixed")
    Unfortunetly not working.

    All of them say "not mixed", but some should say mixed.

    data SHOULD look like:
    CustomerAB | 555 street | type1 | mixed
    CustomerAB | 500 street | type2 | mixed
    CustomerAB | 778 street | type1 | mixed

    CustomerXZ | 111 street | type1 | not mixed
    CustomerXZ | 089 street | type1 | not mixed
    CustomerXZ | 019 street | type1 | not mixed

    but it says "not mixed" for all.

  4. #4
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    177
    Articles
    0
    Excel Version
    2010
    Can you post an actual workbook, please, because that formula should work?
    Circumference of a circle = 2πrē



    ēthe circle's radius

  5. #5
    It works fine for me, some mixed, some not mixed.

  6. #6
    Quote Originally Posted by JoePublic View Post
    Can you post an actual workbook, please, because that formula should work?
    It works! Made a silly typo.

    Thanks so much!

    Now, are we able to edit this formula, so it will leave the cell blank, if the customer name is blank?
    I have many addresses/located that are not assigned to a customer.

    Data would look like:

    CustomerAB | 555 street | type1 | mixed
    CustomerAB | 500 street | type2 | mixed
    CustomerAB | 778 street | type1 | mixed
    ___________| 222 ave | type1 |
    ___________| 322 lane | type2 |
    CustomerXZ | 111 street | type1 | not mixed
    CustomerXZ | 089 street | type1 | not mixed
    CustomerXZ | 019 street | type1 | not mixed


    thanks for the fantastic help.

  7. #7
    Like so

    =IF(A2="","",IF(AND(COUNTIFS(A:A,A2,C:C,"type1")>0,COUNTIFS(A:A,A2,C:C,"type2")>0),"mixed","not mixed"))

  8. #8
    Quote Originally Posted by Bob Phillips View Post
    Like so

    =IF(A2="","",IF(AND(COUNTIFS(A:A,A2,C:C,"type1")>0,COUNTIFS(A:A,A2,C:C,"type2")>0),"mixed","not mixed"))

    Thanks for the quick reply.

    Your solution works great.

    This forum community is very helpful.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •