|
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 -- exceluser2 ------------------------------------------------------------------------ exceluser2's Profile: http://www.excelforum.com/member.php...o&userid=31338 View this thread: http://www.excelforum.com/showthread...hreadid=510203 |
looking for a value in a collumn
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 |
looking for a value in a collumn
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" wrote in message ... 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 |
looking for a value in a collumn
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 |
looking for a value in a collumn
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" wrote in message ... 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 |
looking for a value in a collumn
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") -- exceluser2 ------------------------------------------------------------------------ exceluser2's Profile: http://www.excelforum.com/member.php...o&userid=31338 View this thread: http://www.excelforum.com/showthread...hreadid=510203 |
| All times are GMT +1. The time now is 05:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com