comparing 2 sheets with a range



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



** array formulas need to be entered using the key combination of

Any ideas.
Thanks in advance



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:




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,
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?

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question