Hi,

For this example I will keep everything in the same sheet.
Assume your first data is in the range A1:B1000, the second table is in the
range E1:F10, in cells G1:I1 respectively, enter the following formulas:

=--IF(ISERR(FIND("",F1)),-1E+32,MID(F1,FIND("",F1)+1,IF(ISERR(FIND("<",F1)) ,10,FIND("<",F1))-2))

=--IF(ISERR(FIND("<",F1)),1E+31,MID(F1,FIND("<",F1)+1 ,10))

=SUMPRODUCT(--($B$1:$B$4G1),--($B$1:$B$4<H1),--($A$1:$A$4=E1))

This will give you the count of the number of items of a particular code
which fall in the range indicated in column F.


If this is of some help, please click the Yes button.
--
Thanks,
Shane Devenshire


"rory_r" wrote:

I need to compare 2 columns in 2 sheets but the cells in 1 contains ranges of
acceptability (i,.e 2 < 5).
Long story short here's what I'm doing.

Sheet 1 column 1 contains codes in no order and column 2 varying values.
PRA0121 6
PRA0265 4
PRA0525 3
PRA0530 3

Sheet 2 coulumn 1 contains the codes in order and a range of acceptable
values for each code.
PRA0000 3<6
PRA0001 2<6
PRA0002 1<3
PRA0003 2
PRA0007
PRA0009
This formula works great if I'm using whole numbers but not ranges.
Anything outside these ranges I get a 1 and use conditional formatting to
highlight the cell.


Biff Microsoft Excel MVP suggested this formula for a similar issue in this
forum and I've customized it to fit my needs but only with whole umbers.

sheet 1

=IF(COUNTA(A2:B2),COUNT(MATCH(A2&"*"&B2,Sheet2!A$2 :A$249&"*"&Sheet2!B$2:B$249,0)),"")

sheet2
=IF(COUNTA(A2:B2),COUNT(MATCH(A2&"*"&B2,Sheet1!A$2 :A$53&"*"&Sheet1!B$2:B$53,0)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Any ideas.
Thanks in advance
Rory