Tryring to find right formula

[Joe]

What I have is 5 columns of data
A=Part number B=Cost C=Qty D=Quoted E=Comments
123 1.00 1 History
456 .50 7 History
123 1.50 1
789 2.50 5 History
123 .75 1
456 1.00 7

What I'm trying to do is if the word History is in the
Comments column use the Part number to see if the same
part number exists elsewhere on the spreadsheet without
History in the Comments, if so say yes in the Quoted
column but only in the row that says History. Is this
possible to do? I tried using IF(E2="History",MATCH
(A2,A2:A7),D2,"Yes") but couldn't figure how to get the
thing tot work. Any help is appreciated.
TIA
Joe

 

[Dave R.]

Enter this as an array formula and see if it does what youd like

=+IF(D1="History",
IF(SUM(($A$1:$A$6=$A1)*($D$1:$D$6<>"History"))>0,"Yes",""),"")

 

[Dan E]

Joe,

The following formula should work

=IF(E2="History",IF(SUMPRODUCT(($A$2:$A$7=A2)*($E$2:$E$7<>"History"))>0,"Yes","No"),"")

Explanation
SUMPRODUCT(($A$2:$A$7=A2)*($E$2:$E$7<>"History"))>0
Counts the number of items with column A equal to the
number in A2 AND "History" NOT in column E
If that number is bigger than 0 (ie their is such an item) then
it returns a "Yes" if not it returns a "No". If "History" is not
in column E it returns "" (nothing)

Dan E

 

[Dave R.]

Sorry should be E in place of D there.

Enter this as an array formula and see if it does what youd like

=+IF(D1="History",
IF(SUM(($A$1:$A$6=$A1)*($D$1:$D$6<>"History"))>0,"Yes",""),"")

 

[Joe]

Thanks for the help, it's working great.
Joe