comparing values from a range

[struct]

Hello,

I'm not sure how to approach this...
I have a report with quite a lot of data. I need to do a couple of things with it.

1) I need to look for all records that have the number 7 in column C that don't have a duplicate entry in column A



Simplified Example
------------------------------------------------------
AB45678902CN somedata 7 somedata somedata
AB45678902CN somedata 1 somedata somedata

2) Separately, I need to search for

Items in column A that do not have a corresponding number 7 entry in column C

In both cases, there should be at least two of each entry in column A.

I really hope I've explained this well enough!

Thanks for your input.

[macropod]

Hi struct,

It's not clear from your post what should appear in column A. The first example has a 7 in column C, and there's a 7 in the string in column A. The second example has a 1 in column C, and there's no 1 in the string in column A.

Assuming you need to know when:
There is a 7 in col A but not col A;
There is a 7 in col C but not col A;, and
Neither col A nor col C has a 7,
try the following formula in, say F1, copied down as far as needed:
=IF(AND(C1=7,ISERROR(FIND(7,A1))),"Check Col A",IF(AND(C1<>7,NOT(ISERROR(FIND(7,A1)))),"Chec k Col C",IF(AND(C1<>7,ISERROR(FIND(7,A1))),"Check Cols A & C","")))
If you then sort the data by Col F, you'll have all the questionable records sorted together at either the top or bottom of the list, depending on your sort order.