# comparing 2 sheets with a range

R

#### rory_r

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.
Rory

S

#### ShaneDevenshire

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\$4>G1),--(\$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.

R

#### rory_r

Thanks Shane,
Here's what I have now,
PRA0003 4 PRA0000 >3<6 3 6 1
PRA0028 7 PRA0001 >2<6 2 6 1
PRA0001 4 PRA0002 >1<3 1 3 0
PRA0000 4 PRA0003 >2 2 1E+31 1
PRA0002 7 PRA0007 >3<6 3 6 0
PRA0003 12 PRA0009 >2<6 2 6 0
PRA0007 3 PRA0010 >1<3 1 3 0
PRA0013 4 PRA0013 >3<6 3 6 0
PRA0014 PRA0028 >3<6 3 6 0

What I need is a way of highlighting the entries in column B that do not
meet the criterion in column F.
Any thoughts?
Rory