Thread: [Solved] comparing values from a range
View Single Post
 
Old 04-01-2011, 07:17 PM
macropod's Avatar
macropod macropod is offline Windows 7 32bit Office 2000
Administrator
  
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,338
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default
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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote