software

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: www.excelforum.com/member.php...oamp;userid=31338
View this thread: www.excelforum.com/showthread...hreadid=510203How about

=IF(SUMPRODUCT(--($B$2:$B$7 = B2),--($A$2:$A$7 =
quot;carsquot;)) SUMPRODUCT(--($B$2:$B$7 = B2),--($A$2:$A$7 = quot;trucksquot;))=2,quot;Yquot;,quot;Nquot;)

--
Kevin Vaughnquot;exceluser2quot; wrote:

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

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=quot;carsquot;) ($A$2:$A$200=quot;trucksquot;)gt;0),--($B$2:$B$200=B2))=2,quot;Yesquot;,quot;Nquot;)

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

--
Regards,

Peo Sjoblom

Portland, Oregon

quot;exceluser2quot; gt; wrote
in message ...
gt;
gt; Here is an example of the current sheet
gt;
gt; Type | Site
gt; cars | Automart.com
gt; cars | MSN Autos
gt; trucks| MSN Autos
gt; food | kraft.com
gt; trucks | fordtrucks.com
gt;
gt; I would like to create a new collumn titled match. The function would
gt; match if a site included both types (cars and trucks). Could I have
gt; some help with this function?
gt;
gt; Type | Site | Match
gt; cars | Automart.com | N
gt; cars | MSN Autos | Y
gt; cars | ford.com | Y
gt; trucks| MSN Autos | Y
gt; food | kraft.com | N
gt; trucks | ford.com | Y
gt;
gt;
gt; --
gt; exceluser2
gt; ------------------------------------------------------------------------
gt; exceluser2's Profile:
gt; www.excelforum.com/member.php...oamp;userid=31338
gt; View this thread: www.excelforum.com/showthread...hreadid=510203
gt;
gt;
gt; In the site column, can the sites occur more than once per type of
gt; vehicle,
gt; meaning if there is one MSN Autos for cars and one for trucks, that
gt; would
gt; total 2 thus return yes?
gt;

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: www.excelforum.com/member.php...oamp;userid=31338
View this thread: www.excelforum.com/showthread...hreadid=510203Then this should work copied down

=IF(AND(SUMPRODUCT(--($A$2:$A$200=quot;carsquot;),--($B$2:$B$200=B2))gt;0,SUMPRODUCT(--($A$2:$A$200=quot;trucksquot;),--($B$2:$B$200=B2))gt;0),quot;Yesquot;,quot;Noquot;)

--
Regards,

Peo Sjoblom

Portland, Oregon

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

=IF(AND(SUMPRODUCT(--($A$2:$A$200=quot;carsquot;),--($B$2:$B$200=B2))gt;0,SUMPRODUCT(--($A$2:$A$200=quot;trucksquot;),--($B$2:$B$200=B2))gt;0),quot;Yesquot;,quot;Noquot;)--
exceluser2
------------------------------------------------------------------------
exceluser2's Profile: www.excelforum.com/member.php...oamp;userid=31338
View this thread: www.excelforum.com/showthread...hreadid=510203