PDA

View Full Version : Excel 2007, a formula to check multiple rows for a given customer name

1boredguy
2012-02-28, 04:32 AM
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

JoePublic
2012-02-29, 02:28 PM
For example:
=IF(AND(COUNTIFS(A:A,A2,C:C,"type1")>0,COUNTIFS(A:A,A2,C:C,"type2")>0),"mixed","not mixed")

1boredguy
2012-03-01, 06:04 AM
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.

JoePublic
2012-03-01, 07:33 AM
Can you post an actual workbook, please, because that formula should work?

Bob Phillips
2012-03-01, 08:56 PM
It works fine for me, some mixed, some not mixed.

1boredguy
2012-03-02, 02:05 AM
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.

Bob Phillips
2012-03-02, 02:32 AM
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"))

1boredguy
2012-03-02, 02:55 AM
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"))