find a record with a missing value
 

Hi everybody,
I have an excel worksheet
A B
John XY
John X
John Y
John G
Ed X
Ed Y
Tod XY
....
GHA

in each records I have a name in column A and some value in column C , I
want to write a fomula ( in column D ) to find every record ( an individual )
that doesn't have at least one XY in "column C" , emplyees are sorted by
thier names and there are variable number of reocords for each employee. In
this example Ed should be flagged because it doesn't have at least one XY in
column C.
Any suggestions?
--
Best regards,
Edward

find a record with a missing value
 

Hi Edward

Try the below formula in cell D1 and copy down as required

=IF(SUMPRODUCT(($A$1:$A$100=A1)*($B$1:$B$100="xy") )=0,"Flag","")

Incase you are using XL 2007 check out help on COUNTIFS()

--
Jacob (MVP - Excel)


"Edward" wrote:

Hi everybody,
I have an excel worksheet
A B
John XY
John X
John Y
John G
Ed X
Ed Y
Tod XY
...
GHA

in each records I have a name in column A and some value in column C , I
want to write a fomula ( in column D ) to find every record ( an individual )
that doesn't have at least one XY in "column C" , emplyees are sorted by
thier names and there are variable number of reocords for each employee. In
this example Ed should be flagged because it doesn't have at least one XY in
column C.
Any suggestions?
--
Best regards,
Edward