Finding a number in a range from 1 cell

[Russk68]

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

 

[Peter_SSs]

Does this do what you want?
Select A1:A?? and apply the Conditional Formatting shown.

Excel Workbook
	A	B
1	103
2	10
3	202
4	155	103>101
5	3
6	32	28
7
8		5/202/32
9	5	208>33
10		155
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	1. / Formula is =(A1<>"")*AGGREGATE(15,6,FIND("/"&A1&"/","/"&SUBSTITUTE($B$1:$B$10,">","/")&"/"),1)	Abc

 

[Russk68]

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]

Try this one then.

Excel Workbook
	A	B
1	103
2	10
3	201
4	155	101>103
5	3
6	32	28
7
8	199	5/32/202
9	5	183>200
10		155
CF (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	1. / Formula is =(A1<>"")*AGGREGATE(14,6,IFERROR(FIND("/"&A1&"/","/"&$B$1:$B$10&"/"),0)+IFERROR((A1-LEFT(B$1:B$10,FIND(">",B$1:B$10)-1)>=0),0)*IFERROR((A1-REPLACE(B$1:B$10,1,FIND(">",B$1:B$10),"")<=0),0),1)	Abc

 

[Russk68]

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

Thank you very much!

 

[Peter_SSs]

You are very welcome. Glad to help.

 

[Russk68]

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

 

[Peter_SSs]

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
	A	B	C
1	103		1
2	10		0
3	201		0
4	155	101>103	1
5	3		0
6	32	28	3
7			0
8	199	5/32/202	1
9	5	183>200	1
10		155	0
CF (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	1. / Formula is =C1	Abc

 

[Russk68]

That did it!

Thanks again Peter!

 

[Russk68]

Much speedier!