Finding a number in a range from 1 cell

Russk68

Active Member
Joined
May 1, 2006
Messages
473
Hello all,

I have a column with numbers and each row can contain a # or #># or #/#/#.(101 or 101>105 or 103/106/119)

I also have another column and each row has only 1 number.

For example:
Column A can only contain 1 number in each cell, lets say 103 in A1.
Column B can contain a number or ranges as noted above, lets say 101>105 in B15

I want to use conditional formatting in column A to indicate if 103 is found anywhere in column B.

Possible?

Thanks!

Russ
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,320
Office Version
  1. 365
Platform
  1. Windows
Does this do what you want?
Select A1:A?? and apply the Conditional Formatting shown.

Excel Workbook
AB
1103
210
3202
4155103>101
53
63228
7
85/202/32
95208>33
10155
CF
#VALUE!
 

Russk68

Active Member
Joined
May 1, 2006
Messages
473
That is so very close to what I am looking for.
In row 9 where you have 208>33, (I will always use the lower number on the left 33>208) I need any number in column A that is between 33 & 208 to format as True. So in your example, if 34 was entered in A8, It would format as True.

Hope that makes sense.

Thank you very much for your help!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,320
Office Version
  1. 365
Platform
  1. Windows
Try this one then.

Excel Workbook
AB
1103
210
3201
4155101>103
53
63228
7
81995/32/202
95183>200
10155
CF (2)
#VALUE!
 

Russk68

Active Member
Joined
May 1, 2006
Messages
473

ADVERTISEMENT

Hi Peter,
I have no idea what that all means but it works great!

Thank you very much!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,320
Office Version
  1. 365
Platform
  1. Windows
You are very welcome. Glad to help. :)
 

Russk68

Active Member
Joined
May 1, 2006
Messages
473

ADVERTISEMENT

Hi Peter,
I would appreciate your help again.

I am trying to use your formula in a cell to return a 0 or 1. The reason is that, when typing in a cell in the column, it's very slow because of the complexity among 2200 cells of conditional formatting. I want the conditional formatting to work on 0 or 1 from your formula in a hidden column.

I tried doing this but it's not working as expected. I pasted your formula directly in C1 and dragged down.
Example of correct result:
A1=101 B1=101>103 C1=1

Example of incorrect result:
A2=101 B1=101>103 C1=0

The formula seems to only work in the same row.


After several attempts over many years, I still can't figure out how to paste a screen shot from a Mac.


101 101>103 1
101 105>107 0


Thanks!

Russ
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,320
Office Version
  1. 365
Platform
  1. Windows
The formula needs to be an array formula. Conditional Formatting automatically treats it that way but when you put it in its own cell (C1) you need to confirm it with Ctrl+Shift+Enter, not just Enter. You can then copy it down.

The formula may not just produce 0 or 1 values but higher values as well as shown below. However, the CF formula can still just say "=C1" as 0 values will be treated as False and any other value treated as True.

So here is my sheet again with the old CF removed and this new CF using column C. I'm not sure if it will improve your sheet's performance significantly, but worth a try.

Excel Workbook
ABC
11031
2100
32010
4155101>1031
530
632283
70
81995/32/2021
95183>2001
101550
CF (2)
#VALUE!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,451
Messages
5,528,822
Members
409,839
Latest member
akashsadhu
Top