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.

why not just try autofilter?

--


Gary


"Curious" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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.
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>

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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >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.
> >

=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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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.
> >

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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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.
> > >
>
>

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

--
Regards,
Tom Ogilvy


"Curious" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> =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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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.
>> >
>

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" <(E-Mail Removed)> wrote in message
news:un4BY$$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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.
> >
>
>