Formula needed to determine true activity within duplicates

[emcgaffin]

Background: Every week or so I need to adjust pricing on all of my vehicles. This is a cumbersome task so I have been trying to approve it.

Current: I wrote a simple formula in Column E to determine if a stock # had activity with in a certain time frame: =IF(OR(C1>=Today()-10,D1>=Today()-10),"Yes","No"). The 10 in the previous formula changes so it is not important at all. I just used it for this example. I thought this would allow me to create a pivot table to show which stock #s had recent activity so I could adjust pricing. The issue I just came across is below. Stock number 41074 did have recent activity, however, a different customer had looked at it earlier so I am going to have skewed data because there is a Yes and a No for the same stock number.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Stock #[/TD]
[TD]Created[/TD]
[TD]Engaged[/TD]
[TD]Activity[/TD]
[/TR]
[TR]
[TD]Julian S[/TD]
[TD]41074[/TD]
[TD]9/1/2017[/TD]
[TD]9/3/2017[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Arthur B[/TD]
[TD]41074[/TD]
[TD]8/15/2017[/TD]
[TD]8/28/2017
[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Grace D[/TD]
[TD]BD103[/TD]
[TD]8/10/2017[/TD]
[TD]9/7/2017[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Chris C[/TD]
[TD]CF205[/TD]
[TD]8/1/2017[/TD]
[TD]8/5/2017[/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]


Goal:I would like a formula that would go into the next column (F). The formula would need to look at the stock numbers and determine if any of the duplicate values produce a "Yes". If so, it would need to return Yes for every entry for the stock number even if the value in column E is No. The stock numbers that are truly NO, meaning that all of the duplicates are NO, are just as important as the stock numbers with a Yes. I point this out because I can't just sort the table and remove all of the No's before I run a pivot.

Is this possible?

Thanks for the help!!

Eric

 

[anglais428]

You could use a COUNTIFS formula for this. Assuming your data above starts in cell A1, in cell F2 add =IF(COUNTIFS($B$2:$B$5,B2,$E$2:$E$5,"Yes")>0,"Yes",E2)

This will give Yes, Yes, Yes, No as the results when you fill down.

 

[emcgaffin]

Thanks Anglais42B!!! That works fine. Have a great day!

Eric