Flagging of items

  • Thread starter Thread starter Tiziano
  • Start date Start date
T

Tiziano

Column A shows a long list of part numbers (some occur in more than one
instance); column B shows "EXPD" beside each item that I have expedited
from my supplier. The list is always sorted by part number, and the same
part number could be mentioned multiple times as there could be more
than one purchase order placed for the same part number.

I would like to find a way to place and "X" in column C only if _all_ the
occurrences of the _same_ part number show "EXPD" in column B.
If, for instance, cells A1, A2, A3, A4 show the _same_ part number and
cells B1, B2, B3, B4 _all_ show "EXPD", then cells C1, C2, C3, C4
should _all_ show an "X". But if _any one_ of the cells B1, B2, B3, B4
does not show "EXPD", then there should be no "X" in C1, C2, C3, C4.
Again, please keep in mind that a part number could be mentioned once,
twice, or many more times in column A.

I think this requires a formula that uses arrays, but I am not an expert...

Thanks in advance for all your efforts.
 
Hi
enter the following formula in C1
=IF(COUNTIF($A1:$A1000,A1)=SUMPRODUCT(--($A$1:$A$1000=A1),--($B$1:$B$10
00="EXPD")),"X","")
and copy this formula down for all rows
 
It works perfectly! Thanks for your efforts, Frank.

PS: What does the "--" placed between SUMPRODUCT and ($A$1:$A$1000=A1) do?
I cannot find anything under the Help files in Excel...
----
Tiziano


Frank Kabel said:
Hi
enter the following formula in C1
=IF(COUNTIF($A1:$A1000,A1)=SUMPRODUCT(--($A$1:$A$1000=A1),--($B$1:$B$10
00="EXPD")),"X","")
and copy this formula down for all rows
Click to expand...
 
Hi
this unary operator (double minus) coerces the boolean values to
numbers (TRUE=1/FALSE=0)
 
Back
Top