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

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

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

"ShaneDevenshire" wrote:

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