Count # - Compare - Find - Identify
 

2 Columns about order status

Company A expired
Company A expired
Company A active
Company A active
Company B active
Company C expired
Company D expired
Company D active
.....
.....

A company can have multiple expired orders but only one active order.
The purpose is to identify the company with multiple active orders and
fix it manually.

Thanks in advance.

Count # - Compare - Find - Identify
 

why not just try autofilter?

--


Gary


"Curious" wrote in message
oups.com...
2 Columns about order status

Company A expired
Company A expired
Company A active
Company A active
Company B active
Company C expired
Company D expired
Company D active
....
....

A company can have multiple expired orders but only one active order.
The purpose is to identify the company with multiple active orders and
fix it manually.

Thanks in advance.

Count # - Compare - Find - Identify
 

In column C

=IF(SUMPRODUCT(--($A$1:L$A$20=A1),--($B$1:$B$20="Active")),"Dup","")

and copy down

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Curious" wrote in message
oups.com...
2 Columns about order status

Company A expired
Company A expired
Company A active
Company A active
Company B active
Company C expired
Company D expired
Company D active
....
....

A company can have multiple expired orders but only one active order.
The purpose is to identify the company with multiple active orders and
fix it manually.

Thanks in advance.

Count # - Compare - Find - Identify
 

This is a large database. If I use the autofilter, I have to look at
each company name to ensure that there is only one active order.

Thanks


Gary Keramidas wrote:
why not just try autofilter?

--


Gary


"Curious" wrote in message
oups.com...
2 Columns about order status

Company A expired
Company A expired
Company A active
Company A active
Company B active
Company C expired
Company D expired
Company D active
....
....

A company can have multiple expired orders but only one active order.
The purpose is to identify the company with multiple active orders and
fix it manually.

Thanks in advance.

Count # - Compare - Find - Identify
 

=IF(SUMPRODUCT(-($B$8:$B$1000=B610),-($D$8:$D$1000="Active")),"Dup","")

does not work

It assigns "dup" to every order, except those companys that have no
active orders.




Bob Phillips wrote:
In column C

=IF(SUMPRODUCT(--($A$1:L$A$20=A1),--($B$1:$B$20="Active")),"Dup","")

and copy down

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Curious" wrote in message
oups.com...
2 Columns about order status

Company A expired
Company A expired
Company A active
Company A active
Company B active
Company C expired
Company D expired
Company D active
....
....

A company can have multiple expired orders but only one active order.
The purpose is to identify the company with multiple active orders and
fix it manually.

Thanks in advance.

Count # - Compare - Find - Identify
 

I don't like adding columns, but if col c =A&B then you could use conditional
formatiing to look for where Colum B="active" and then count how many times
the current cell value is in column c, & colour it.
Use this conditional formatting formula in C1

=IF(B1="active",IF(COUNTIF(C:C,C1)1,1,0),0) this will cause the cell to
conditionally format where a company has more than one active order.

"Curious" wrote:

=IF(SUMPRODUCT(-($B$8:$B$1000=B610),-($D$8:$D$1000="Active")),"Dup","")

does not work

It assigns "dup" to every order, except those companys that have no
active orders.




Bob Phillips wrote:
In column C

=IF(SUMPRODUCT(--($A$1:L$A$20=A1),--($B$1:$B$20="Active")),"Dup","")

and copy down

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Curious" wrote in message
oups.com...
2 Columns about order status

Company A expired
Company A expired
Company A active
Company A active
Company B active
Company C expired
Company D expired
Company D active
....
....

A company can have multiple expired orders but only one active order.
The purpose is to identify the company with multiple active orders and
fix it manually.

Thanks in advance.

Count # - Compare - Find - Identify
 

=IF(SUMPRODUCT(-($B$8:$B$1000=B610),-($D$8:$D$1000="Active"))1,"Dup","")

--
Regards,
Tom Ogilvy


"Curious" wrote in message
oups.com...
=IF(SUMPRODUCT(-($B$8:$B$1000=B610),-($D$8:$D$1000="Active")),"Dup","")

does not work

It assigns "dup" to every order, except those companys that have no
active orders.




Bob Phillips wrote:
In column C

=IF(SUMPRODUCT(--($A$1:L$A$20=A1),--($B$1:$B$20="Active")),"Dup","")

and copy down

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Curious" wrote in message
oups.com...
2 Columns about order status

Company A expired
Company A expired
Company A active
Company A active
Company B active
Company C expired
Company D expired
Company D active
....
....

A company can have multiple expired orders but only one active order.
The purpose is to identify the company with multiple active orders and
fix it manually.

Thanks in advance.

Count # - Compare - Find - Identify
 

Sorry, I meant that the count should be counted as greater thajn 1

=IF(SUMPRODUCT(--($A$1:L$A$20=$A1),--($B$1:$B$20="Active"))1,"Dup","")

If you just want to highlight the 2nd (and 3rd etc.) instance, then use

=IF(SUMPRODUCT(--($A$1:$A1=$A1),--($B$1:$B1="Active"))1,"Dup","")


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Bob Phillips" wrote in message
...
In column C

=IF(SUMPRODUCT(--($A$1:L$A$20=A1),--($B$1:$B$20="Active")),"Dup","")

and copy down

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Curious" wrote in message
oups.com...
2 Columns about order status

Company A expired
Company A expired
Company A active
Company A active
Company B active
Company C expired
Company D expired
Company D active
....
....

A company can have multiple expired orders but only one active order.
The purpose is to identify the company with multiple active orders and
fix it manually.

Thanks in advance.