Formula help

[serge]

I need couple formulas that would return ( 1 ) when they find :

11111 or 2111 or 221 or 311 or 32 or 41 or 5

Those digits could be in different order !!!



<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:26px;"><col style="width:26px;"><col style="width:26px;"><col style="width:26px;"><col style="width:26px;"><col style="width:26px;"><col style="width:19px;"><col style="width:42px;"><col style="width:42px;"><col style="width:42px;"><col style="width:42px;"><col style="width:42px;"><col style="width:42px;"><col style="width:42px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td> </td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td><td>I</td><td>J</td><td>K</td><td>L</td><td>M</td><td>N</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">11111</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">2111</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">221</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">311</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">32</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">41</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">5</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="background-color:#ffffcc; text-align:center; ">1</td><td style="background-color:#ffffcc; text-align:center; ">2</td><td style="background-color:#ffffcc; text-align:center; ">0</td><td style="background-color:#ffffcc; text-align:center; ">0</td><td style="background-color:#ffffcc; text-align:center; ">1</td><td style="background-color:#ffffcc; text-align:center; ">1</td><td> </td><td> </td><td style="text-align:center; ">1</td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="background-color:#ffffcc; text-align:center; ">0</td><td style="background-color:#ffffcc; text-align:center; ">2</td><td style="background-color:#ffffcc; text-align:center; ">1</td><td style="background-color:#ffffcc; text-align:center; ">0</td><td style="background-color:#ffffcc; text-align:center; ">0</td><td style="background-color:#ffffcc; text-align:center; ">2</td><td> </td><td> </td><td> </td><td style="text-align:center; ">1</td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="background-color:#ffffcc; text-align:center; ">0</td><td style="background-color:#ffffcc; text-align:center; ">3</td><td style="background-color:#ffffcc; text-align:center; ">1</td><td style="background-color:#ffffcc; text-align:center; ">0</td><td style="background-color:#ffffcc; text-align:center; ">1</td><td style="background-color:#ffffcc; text-align:center; ">0</td><td> </td><td> </td><td> </td><td> </td><td style="text-align:center; ">1</td><td> </td><td> </td><td> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td style="background-color:#ffffcc; text-align:center; ">1</td><td style="background-color:#ffffcc; text-align:center; ">1</td><td style="background-color:#ffffcc; text-align:center; ">0</td><td style="background-color:#ffffcc; text-align:center; ">1</td><td style="background-color:#ffffcc; text-align:center; ">0</td><td style="background-color:#ffffcc; text-align:center; ">2</td><td> </td><td> </td><td style="text-align:center; ">1</td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td style="background-color:#ffffcc; text-align:center; ">0</td><td style="background-color:#ffffcc; text-align:center; ">0</td><td style="background-color:#ffffcc; text-align:center; ">2</td><td style="background-color:#ffffcc; text-align:center; ">1</td><td style="background-color:#ffffcc; text-align:center; ">0</td><td style="background-color:#ffffcc; text-align:center; ">2</td><td> </td><td> </td><td> </td><td style="text-align:center; ">1</td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td style="background-color:#ffffcc; text-align:center; ">0</td><td style="background-color:#ffffcc; text-align:center; ">0</td><td style="background-color:#ffffcc; text-align:center; ">2</td><td style="background-color:#ffffcc; text-align:center; ">2</td><td style="background-color:#ffffcc; text-align:center; ">0</td><td style="background-color:#ffffcc; text-align:center; ">1</td><td> </td><td> </td><td> </td><td style="text-align:center; ">1</td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td style="background-color:#ffffcc; text-align:center; ">0</td><td style="background-color:#ffffcc; text-align:center; ">0</td><td style="background-color:#ffffcc; text-align:center; ">5</td><td style="background-color:#ffffcc; text-align:center; ">0</td><td style="background-color:#ffffcc; text-align:center; ">0</td><td style="background-color:#ffffcc; text-align:center; ">0</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td style="text-align:center; ">1</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">10</td><td style="background-color:#ffffcc; text-align:center; ">1</td><td style="background-color:#ffffcc; text-align:center; ">1</td><td style="background-color:#ffffcc; text-align:center; ">0</td><td style="background-color:#ffffcc; text-align:center; ">0</td><td style="background-color:#ffffcc; text-align:center; ">1</td><td style="background-color:#ffffcc; text-align:center; ">2</td><td> </td><td> </td><td style="text-align:center; ">1</td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">11</td><td style="background-color:#ffffcc; text-align:center; ">2</td><td style="background-color:#ffffcc; text-align:center; ">1</td><td style="background-color:#ffffcc; text-align:center; ">1</td><td style="background-color:#ffffcc; text-align:center; ">0</td><td style="background-color:#ffffcc; text-align:center; ">0</td><td style="background-color:#ffffcc; text-align:center; ">1</td><td> </td><td> </td><td style="text-align:center; ">1</td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">12</td><td style="background-color:#ffffcc; text-align:center; ">0</td><td style="background-color:#ffffcc; text-align:center; ">3</td><td style="background-color:#ffffcc; text-align:center; ">0</td><td style="background-color:#ffffcc; text-align:center; ">0</td><td style="background-color:#ffffcc; text-align:center; ">2</td><td style="background-color:#ffffcc; text-align:center; ">0</td><td> </td><td> </td><td> </td><td> </td><td> </td><td style="text-align:center; ">1</td><td> </td><td> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">13</td><td style="background-color:#ffffcc; text-align:center; ">1</td><td style="background-color:#ffffcc; text-align:center; ">0</td><td style="background-color:#ffffcc; text-align:center; ">0</td><td style="background-color:#ffffcc; text-align:center; ">0</td><td style="background-color:#ffffcc; text-align:center; ">4</td><td style="background-color:#ffffcc; text-align:center; ">0</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td style="text-align:center; ">1</td><td> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">14</td><td style="background-color:#ffffcc; text-align:center; ">0</td><td style="background-color:#ffffcc; text-align:center; ">1</td><td style="background-color:#ffffcc; text-align:center; ">1</td><td style="background-color:#ffffcc; text-align:center; ">1</td><td style="background-color:#ffffcc; text-align:center; ">1</td><td style="background-color:#ffffcc; text-align:center; ">1</td><td> </td><td style="text-align:center; ">1</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr></tbody></table>
Thank you.


Excel tables to the web >> Excel Jeanie HTML 4

 

[mikerickson]

Here is a method with names.
Select H3 and define these names

Name: Aremoved RefersTo: =SUBSTITUTE(Sheet1!H$1,Sheet1!$A3,"",1)
Name: Bremoved RefersTo: =SUBSTITUTE(Aremoved,Sheet1!$B3,"",1)
Name: Cremoved RefersTo: =SUBSTITUTE(Bremoved,Sheet1!$C3,"",1)
Name: Dremoved RefersTo: =SUBSTITUTE(Cremoved,Sheet1!$D3,"",1)
Name: Eremoved RefersTo: =SUBSTITUTE(Dremoved,Sheet1!$E3,"",1)
Name: Fremoved RefersTo: =SUBSTITUTE(Eremoved,Sheet1!$F3,"",1)

Then put the formula
=IF(Fremoved="", 1, "") in H3 and drag down and right.

Or you could combine them all into a monster formula.

 

[serge]

Thank you Mike for responding, but I don't understand your method !!
I'm not an expert like you with Excel, and if the formula is to complicated I will try to organize my file in a different way.

Thank you.

 

[mikerickson]

Define the names as indicated, Help will explain how. Put the formula in the cell.

 

[energman58]

Brilliant - I would never have thought of that approach!

 

[Peter_SSs]

Mike's idea does seem a good one. The same idea could also be done directly with a formula, which may appeal to the OP if Mike's Names implementation is too difficult.

Formula in H3 copied down and across.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1								11111	2111	221	311	32	41	5
2
3	1	2	0	0	1	1			1
4	0	2	1	0	0	2				1
5	0	3	1	0	1	0					1
6	1	1	0	1	0	2			1
7	0	0	2	1	0	2				1
8	0	0	2	2	0	1				1
9	0	0	5	0	0	0								1
10	1	1	0	0	1	2			1
11	2	1	1	0	0	1			1
12	0	3	0	0	2	0						1
13	1	0	0	0	4	0							1
14	0	1	1	1	1	1		1
Combos 2

If the values in H1, I1, J1 , ... each have their digits arranged from largest to smallest, as they are with the sample data, then this approach may also be suitable.

Formula in G3 copied down.
Formula in H3 copied down and across.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1								11111	2111	221	311	32	41	5
2
3	1	2	0	0	1	1	211100		1
4	0	2	1	0	0	2	221000			1
5	0	3	1	0	1	0	311000				1
6	1	1	0	1	0	2	211100		1
7	0	0	2	1	0	2	221000			1
8	0	0	2	2	0	1	221000			1
9	0	0	5	0	0	0	500000							1
10	1	1	0	0	1	2	211100		1
11	2	1	1	0	0	1	211100		1
12	0	3	0	0	2	0	320000					1
13	1	0	0	0	4	0	410000						1
14	0	1	1	1	1	1	111110	1
Combos 3

I do have a question:
Would it be possible to have in row 1 '221' and '21'?
If so, all the methods suggested so far would return a '1' in both those columns for the sample data in A3:F3. Would that be the correct results?

 

[serge]

Mike and Peter,

You are both incredible, you did a marvelous job with those formulas, that help me a lot in my work.

Thank you so much for the help.

Serge.