I'm trying to compare 2 sheets and highlight any discrapancies from the first
sheet.
Currently sheet 1 (IM) has a column of procedures, a column describing each
procedure and a column showing how many images are acceptable per
procedure(column L).(ie 1<4).

Sheet 2 is copy/pasted from another application to show Patient ID#, name
Procedure code body part description actual image number taken for this
study.
I need to highlight any studies who's image count falls outside that of the
range in the IM sheet coumn L.

This is the formula I'm using to compare the columns and use conditional
formatting to highlight discrepancies in image number, however it only uses
the last number when I use a range. for example 4 when I use 1<4.

=IF(COUNTA(D2:G2),COUNT(MATCH(D2&"*"&G2,IM!A$2:A$1 151&"*"&IM!L$2:L$1151,0)),"")

How do I have the cell highlight if the actual number of images fall outside
the range in column L on the worksheet called IM?
Go easy on me I copied and tweaked this formula from another post.
Thank you in advance for your help.

Rory.

What I would do:
(Assumption Range is given as m<n for all cases... )
IM Sheet
column of procedures is Col A
Sheet2
Procedure code is in Col A (same as Col A of IM)
image number is in B

1. Use
=VLOOKUP(A1,IM!A:L,12,False) to get the Range say in G1 of Sheet2
=MID(G1,2,FIND("",G1)-2) to get the lower limit in H1
=MID(G1,FIND("",G1)+1,LEN(G1)-FIND("",G1)) to get the upper limit in I1
Copy formulae down till end of your data set

2. Then you can use the conditional formatting as follows;
Select the range
Choose Formula Is
enter condition as
=OR($B1<$H1,$B1$I1)

If I were you, I will split the range (<14) in two columns (Data|Text to
Columns, delimiter as < in first pass and in the second pass).

Once you get the hang of it then you can be creative and combine formulae
directly into the condition in FORMULA IS.
"rory_r" wrote:

I'm trying to compare 2 sheets and highlight any discrapancies from the first
sheet.
Currently sheet 1 (IM) has a column of procedures, a column describing each
procedure and a column showing how many images are acceptable per
procedure(column L).(ie 1<4).

Sheet 2 is copy/pasted from another application to show Patient ID#, name
Procedure code body part description actual image number taken for this
study.
I need to highlight any studies who's image count falls outside that of the
range in the IM sheet coumn L.

This is the formula I'm using to compare the columns and use conditional
formatting to highlight discrepancies in image number, however it only uses
the last number when I use a range. for example 4 when I use 1<4.

=IF(COUNTA(D2:G2),COUNT(MATCH(D2&"*"&G2,IM!A$2:A$1 151&"*"&IM!L$2:L$1151,0)),"")

How do I have the cell highlight if the actual number of images fall outside
the range in column L on the worksheet called IM?
Go easy on me I copied and tweaked this formula from another post.
Thank you in advance for your help.

Rory.

Thanks Sheeloo for your help.
I did tweak the formulas for my columns to read
=VLOOKUP(A2,IM!A:L,12,FALSE)
These locate the entries from A2,IM for G2 beautifully however, if I have
blank entries they return a zero in column G, which throws off the other 2
columns with '#VALUE!' errors.

=MID(G2,2,FIND("",G2))--------I'm not sure why the -2 was necessary but his
seems to work beter without it.
=MID(G2,FIND("<",G2)+1,LEN(G2)-FIND("<",G2))-- this iworks great too.

The biggest issue I'm having now is when using
=OR($B2<$H2,$B2$I2)
the format works when I add the value into the cell but not when the same
value is returned from the formula automatically.
i.e. If the formula returns a 3 the formatting works every time even if it
shouldn't, but if I type in a 3 it works correctly.
Any ideas?
Hope this makes sense.
Again thanks so much for your help.
Rory



"Sheeloo" wrote:

What I would do:
(Assumption Range is given as m<n for all cases... )
IM Sheet
column of procedures is Col A
Sheet2
Procedure code is in Col A (same as Col A of IM)
image number is in B

1. Use
=VLOOKUP(A1,IM!A:L,12,False) to get the Range say in G1 of Sheet2
=MID(G1,2,FIND("",G1)-2) to get the lower limit in H1
=MID(G1,FIND("",G1)+1,LEN(G1)-FIND("",G1)) to get the upper limit in I1
Copy formulae down till end of your data set

2. Then you can use the conditional formatting as follows;
Select the range
Choose Formula Is
enter condition as
=OR($B1<$H1,$B1$I1)

If I were you, I will split the range (<14) in two columns (Data|Text to
Columns, delimiter as < in first pass and in the second pass).

Once you get the hang of it then you can be creative and combine formulae
directly into the condition in FORMULA IS.
"rory_r" wrote:

I'm trying to compare 2 sheets and highlight any discrapancies from the first
sheet.
Currently sheet 1 (IM) has a column of procedures, a column describing each
procedure and a column showing how many images are acceptable per
procedure(column L).(ie 1<4).

Sheet 2 is copy/pasted from another application to show Patient ID#, name
Procedure code body part description actual image number taken for this
study.
I need to highlight any studies who's image count falls outside that of the
range in the IM sheet coumn L.

This is the formula I'm using to compare the columns and use conditional
formatting to highlight discrepancies in image number, however it only uses
the last number when I use a range. for example 4 when I use 1<4.

=IF(COUNTA(D2:G2),COUNT(MATCH(D2&"*"&G2,IM!A$2:A$1 151&"*"&IM!L$2:L$1151,0)),"")

How do I have the cell highlight if the actual number of images fall outside
the range in column L on the worksheet called IM?
Go easy on me I copied and tweaked this formula from another post.
Thank you in advance for your help.

Rory.

=MID(G1,2,FIND("",G1)-2) to get the lower limit in H1
Sorry for the confusion - I made a typo in the above line
It should be
=MID(G2,2,FIND("<",G2)-2) 'changed 1 to 2 as you have header row
It starts to extract at position 2 which is just after "" then
it finds the position of "<" which is 4 in "10<15"
so you need 2 characters (4-2)

To avoid the #Value problem use
=IF(G2=0,"What you want in this case",MID formula)

I had given all formulae assuming there was no header row...

To get the conditional formatting right
Select the range you want to highlight with conditional formatting and then
enter
=OR($B2<$H2,$B2$I2) and choose formatting
(I am assuming that row 2 is the first row selected)

This will highlight if value in B is less than value in H or greater than
value in I
for any given row
[2 will adjust to row no...]

Make sure that Excel has not added nay quotes around your formula

[You can also split the condition as follows
=$B2<$H2 as First condition and choose highlighting

=$B2$I2 as Second condition and choose highlighting
You can choose same highlighiting for both]


"rory_r" wrote:

Thanks Sheeloo for your help.
I did tweak the formulas for my columns to read
=VLOOKUP(A2,IM!A:L,12,FALSE)
These locate the entries from A2,IM for G2 beautifully however, if I have
blank entries they return a zero in column G, which throws off the other 2
columns with '#VALUE!' errors.

=MID(G2,2,FIND("",G2))--------I'm not sure why the -2 was necessary but his
seems to work beter without it.
=MID(G2,FIND("<",G2)+1,LEN(G2)-FIND("<",G2))-- this iworks great too.

The biggest issue I'm having now is when using
=OR($B2<$H2,$B2$I2)
the format works when I add the value into the cell but not when the same
value is returned from the formula automatically.
i.e. If the formula returns a 3 the formatting works every time even if it
shouldn't, but if I type in a 3 it works correctly.
Any ideas?
Hope this makes sense.
Again thanks so much for your help.
Rory



"Sheeloo" wrote:

What I would do:
(Assumption Range is given as m<n for all cases... )
IM Sheet
column of procedures is Col A
Sheet2
Procedure code is in Col A (same as Col A of IM)
image number is in B

1. Use
=VLOOKUP(A1,IM!A:L,12,False) to get the Range say in G1 of Sheet2
=MID(G1,2,FIND("",G1)-2) to get the lower limit in H1
=MID(G1,FIND("",G1)+1,LEN(G1)-FIND("",G1)) to get the upper limit in I1
Copy formulae down till end of your data set

2. Then you can use the conditional formatting as follows;
Select the range
Choose Formula Is
enter condition as
=OR($B1<$H1,$B1$I1)

If I were you, I will split the range (<14) in two columns (Data|Text to
Columns, delimiter as < in first pass and in the second pass).

Once you get the hang of it then you can be creative and combine formulae
directly into the condition in FORMULA IS.
"rory_r" wrote:

I'm trying to compare 2 sheets and highlight any discrapancies from the first
sheet.
Currently sheet 1 (IM) has a column of procedures, a column describing each
procedure and a column showing how many images are acceptable per
procedure(column L).(ie 1<4).

Sheet 2 is copy/pasted from another application to show Patient ID#, name
Procedure code body part description actual image number taken for this
study.
I need to highlight any studies who's image count falls outside that of the
range in the IM sheet coumn L.

This is the formula I'm using to compare the columns and use conditional
formatting to highlight discrepancies in image number, however it only uses
the last number when I use a range. for example 4 when I use 1<4.

=IF(COUNTA(D2:G2),COUNT(MATCH(D2&"*"&G2,IM!A$2:A$1 151&"*"&IM!L$2:L$1151,0)),"")

How do I have the cell highlight if the actual number of images fall outside
the range in column L on the worksheet called IM?
Go easy on me I copied and tweaked this formula from another post.
Thank you in advance for your help.

Rory.

Again, thanks so much,
This is really taking shape.


"Sheeloo" wrote:

=MID(G1,2,FIND("",G1)-2) to get the lower limit in H1
Sorry for the confusion - I made a typo in the above line
It should be
=MID(G2,2,FIND("<",G2)-2) 'changed 1 to 2 as you have header row
It starts to extract at position 2 which is just after "" then
it finds the position of "<" which is 4 in "10<15"
so you need 2 characters (4-2)

To avoid the #Value problem use
=IF(G2=0,"What you want in this case",MID formula)

I had given all formulae assuming there was no header row...

To get the conditional formatting right
Select the range you want to highlight with conditional formatting and then
enter
=OR($B2<$H2,$B2$I2) and choose formatting
(I am assuming that row 2 is the first row selected)

This will highlight if value in B is less than value in H or greater than
value in I
for any given row
[2 will adjust to row no...]

Make sure that Excel has not added nay quotes around your formula

[You can also split the condition as follows
=$B2<$H2 as First condition and choose highlighting

=$B2$I2 as Second condition and choose highlighting
You can choose same highlighiting for both]


"rory_r" wrote:

Thanks Sheeloo for your help.
I did tweak the formulas for my columns to read
=VLOOKUP(A2,IM!A:L,12,FALSE)
These locate the entries from A2,IM for G2 beautifully however, if I have
blank entries they return a zero in column G, which throws off the other 2
columns with '#VALUE!' errors.

=MID(G2,2,FIND("",G2))--------I'm not sure why the -2 was necessary but his
seems to work beter without it.
=MID(G2,FIND("<",G2)+1,LEN(G2)-FIND("<",G2))-- this iworks great too.

The biggest issue I'm having now is when using
=OR($B2<$H2,$B2$I2)
the format works when I add the value into the cell but not when the same
value is returned from the formula automatically.
i.e. If the formula returns a 3 the formatting works every time even if it
shouldn't, but if I type in a 3 it works correctly.
Any ideas?
Hope this makes sense.
Again thanks so much for your help.
Rory



"Sheeloo" wrote:

What I would do:
(Assumption Range is given as m<n for all cases... )
IM Sheet
column of procedures is Col A
Sheet2
Procedure code is in Col A (same as Col A of IM)
image number is in B

1. Use
=VLOOKUP(A1,IM!A:L,12,False) to get the Range say in G1 of Sheet2
=MID(G1,2,FIND("",G1)-2) to get the lower limit in H1
=MID(G1,FIND("",G1)+1,LEN(G1)-FIND("",G1)) to get the upper limit in I1
Copy formulae down till end of your data set

2. Then you can use the conditional formatting as follows;
Select the range
Choose Formula Is
enter condition as
=OR($B1<$H1,$B1$I1)

If I were you, I will split the range (<14) in two columns (Data|Text to
Columns, delimiter as < in first pass and in the second pass).

Once you get the hang of it then you can be creative and combine formulae
directly into the condition in FORMULA IS.
"rory_r" wrote:

I'm trying to compare 2 sheets and highlight any discrapancies from the first
sheet.
Currently sheet 1 (IM) has a column of procedures, a column describing each
procedure and a column showing how many images are acceptable per
procedure(column L).(ie 1<4).

Sheet 2 is copy/pasted from another application to show Patient ID#, name
Procedure code body part description actual image number taken for this
study.
I need to highlight any studies who's image count falls outside that of the
range in the IM sheet coumn L.

This is the formula I'm using to compare the columns and use conditional
formatting to highlight discrepancies in image number, however it only uses
the last number when I use a range. for example 4 when I use 1<4.

=IF(COUNTA(D2:G2),COUNT(MATCH(D2&"*"&G2,IM!A$2:A$1 151&"*"&IM!L$2:L$1151,0)),"")

How do I have the cell highlight if the actual number of images fall outside
the range in column L on the worksheet called IM?
Go easy on me I copied and tweaked this formula from another post.
Thank you in advance for your help.

Rory.