looking for a value in a collumn

Here is an example of the current sheet

Type | Site
cars | Automart.com
cars | MSN Autos
trucks| MSN Autos
food | kraft.com
trucks | fordtrucks.com

I would like to create a new collumn titled match. The function would match if a site included both types (cars and trucks). Could I have some help with this function?

Type | Site | Match
cars | Automart.com | N
cars | MSN Autos | Y
cars | ford.com | Y
trucks| MSN Autos | Y
food | kraft.com | N
trucks | ford.com | Y

How about

=IF(SUMPRODUCT(--($B$2:$B$7 = B2),--($A$2:$A$7 =
"cars"))+SUMPRODUCT(--($B$2:$B$7 = B2),--($A$2:$A$7 = "trucks"))=2,"Y","N")

--
Kevin Vaughn


"exceluser2" wrote:

>
> Here is an example of the current sheet
>
> Type | Site
> cars | Automart.com
> cars | MSN Autos
> trucks| MSN Autos
> food | kraft.com
> trucks | fordtrucks.com
>
> I would like to create a new collumn titled match. The function would
> match if a site included both types (cars and trucks). Could I have
> some help with this function?
>
> Type | Site | Match
> cars | Automart.com | N
> cars | MSN Autos | Y
> cars | ford.com | Y
> trucks| MSN Autos | Y
> food | kraft.com | N
> trucks | ford.com | Y
>
>
> --
> exceluser2
> ------------------------------------------------------------------------
> exceluser2's Profile: http://www.excelforum.com/member.php...o&userid=31338
> View this thread: http://www.excelforum.com/showthread...hreadid=510203
>
>

In the site column, can the sites occur more than once per type of vehicle,
meaning if there is one MSN Autos for cars and one for trucks, that would
total 2 thus return yes?

=IF(SUMPRODUCT(--(($A$2:$A$200="cars")+($A$2:$A$200="trucks")>0),--($B$2:$B$200=B2))=2,"Yes","N")

where B2 is the first dot com site, copy down as long as needed

--
Regards,

Peo Sjoblom

Portland, Oregon




"exceluser2" <[email protected]> wrote
in message news:[email protected]...
>
> Here is an example of the current sheet
>
> Type | Site
> cars | Automart.com
> cars | MSN Autos
> trucks| MSN Autos
> food | kraft.com
> trucks | fordtrucks.com
>
> I would like to create a new collumn titled match. The function would
> match if a site included both types (cars and trucks). Could I have
> some help with this function?
>
> Type | Site | Match
> cars | Automart.com | N
> cars | MSN Autos | Y
> cars | ford.com | Y
> trucks| MSN Autos | Y
> food | kraft.com | N
> trucks | ford.com | Y
>
>
> --
> exceluser2
> ------------------------------------------------------------------------
> exceluser2's Profile:
> http://www.excelforum.com/member.php...o&userid=31338
> View this thread: http://www.excelforum.com/showthread...hreadid=510203
>

In the site column, can the sites occur more than once per type of vehicle,
meaning if there is one MSN Autos for cars and one for trucks, that would
total 2 thus return yes?

Yes sites can occur more then once.

Example

Type | Site | Match
cars | Automart.com | N
cars | MSN Autos | Y
cars | ford.com | Y
trucks| MSN Autos | Y
food | kraft.com | N
trucks | ford.com | Y
cars | ford.com | Y
cars | ford.com | Y
cars | ford.com | Y
cars | Automart.com | N

Also Sites should be Y even if for types that are not cars and truck if the above condition is true.

food | kraft.com | N
food | ford.com | Y
food | ford.com | Y
food | ford.com | Y
food | ford.com | Y
food | Automart.com | N

Thanks for the help but it is still not what I need.

Then this should work copied down

=IF(AND(SUMPRODUCT(--($A$2:$A$200="cars"),--($B$2:$B$200=B2))>0,SUMPRODUCT(--($A$2:$A$200="trucks"),--($B$2:$B$200=B2))>0),"Yes","No")

--
Regards,

Peo Sjoblom

Portland, Oregon




"exceluser2" <[email protected]> wrote
in message news:[email protected]...
>
>>
>> In the site column, can the sites occur more than once per type of
>> vehicle,
>> meaning if there is one MSN Autos for cars and one for trucks, that
>> would
>> total 2 thus return yes?
>>
>
> Yes sites can occur more then once.
>
> Example
>
> Type | Site | Match
> cars | Automart.com | N
> cars | MSN Autos | Y
> cars | ford.com | Y
> trucks| MSN Autos | Y
> food | kraft.com | N
> trucks | ford.com | Y
> cars | ford.com | Y
> cars | ford.com | Y
> cars | ford.com | Y
> cars | Automart.com | N
>
> Also Sites should be Y even if for types that are not cars and truck if
> the above condition is true.
>
> food | kraft.com | N
> food | ford.com | Y
> food | ford.com | Y
> food | ford.com | Y
> food | ford.com | Y
> food | Automart.com | N
>
> Thanks for the help but it is still not what I need.
>
>
> --
> exceluser2
> ------------------------------------------------------------------------
> exceluser2's Profile:
> http://www.excelforum.com/member.php...o&userid=31338
> View this thread: http://www.excelforum.com/showthread...hreadid=510203
>

This works! Thank you..

=IF(AND(SUMPRODUCT(--($A$2:$A$200="cars"),--($B$2:$B$200=B2))>0,SUMPRODUCT(--($A$2:$A$200="trucks"),--($B$2:$B$200=B2))>0),"Yes","No")