CF with leading 0.

[serge]

I need a CF formula to solve the highlighting in the yellow table on the right, I had a similar post before which I tried to used the formula from it but doesn't work !!! so if someone can help ?

Thank you.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
1			0	1	2	3	4	5	6	7	8	9		0	1	2	3	4	5	6	7	8	9		Arrange from.
2
3	7		0	1	2	3	4	5	6	7	8	9		2	1	4	6	7	18	8	69	100	100		1	2	4	6	7	8	18	69	100	100
4	0		0	1	2	3	4	5	6	7	8	9		3	2	5	7	8	19	9	1	101	101		1	2	3	5	7	8	9	19	101	101
5	1		0	1	2	3	4	5	6	7	8	9		1	3	6	8	9	20	10	2	102	102		1	2	3	6	8	9	10	20	102	102
6	5		0	1	2	3	4	5	6	7	8	9		2	1	7	9	10	21	11	3	103	103		1	2	3	7	9	10	11	21	103	103
7	3		0	1	2	3	4	5	6	7	8	9		3	2	8	10	11	1	12	4	104	104		1	2	3	4	8	10	11	12	104	104
8	4		0	1	2	3	4	5	6	7	8	9		4	3	9	1	12	2	13	5	105	105		1	2	3	4	5	9	12	13	105	105
9	4		0	1	2	3	4	5	6	7	8	9		5	4	10	2	1	3	14	6	106	106		1	2	3	4	5	6	10	14	106	106
10	1		0	1	2	3	4	5	6	7	8	9		6	5	11	3	1	4	15	7	107	107		1	3	4	5	6	7	11	15	107	107
11	1		0	1	2	3	4	5	6	7	8	9		7	1	12	4	2	5	16	8	108	108		1	2	4	5	7	8	12	16	108	108
12	0		0	1	2	3	4	5	6	7	8	9		8	1	13	5	3	6	17	9	109	109		1	3	5	6	8	9	13	17	109	109
13	1		0	1	2	3	4	5	6	7	8	9		1	2	14	6	4	7	18	10	110	110		1	2	4	6	7	10	14	18	110	110
14
Sheet3

 

[serge]

You should have it now.

 

[serge]

Would someone have different formulas than Mark, that would give me the result I'm looking for, for the right table in yellow, please.

I tried and tried his formulas, even on a brain new sheet just in case my number would not be a number value on the previous sheet and nothing, I can't get it to work ???

Thanks.

 

[markmzz]

You should have it now.

Send now, check.

 

[markmzz]

Try this:


<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>Y</th><th>Z</th><th>AA</th><th>AB</th><th>AC</th><th>AD</th><th>AE</th><th>AF</th><th>AG</th><th>AH</th><th>AI</th><th>AJ</th><th>AK</th><th>AL</th><th>AM</th><th>AN</th><th>AO</th><th>AP</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #FAC090;;"></td><td style="text-align: center;background-color: #FAC090;;"></td><td style="text-align: center;background-color: #FAC090;;"></td><td style="text-align: center;background-color: #FAC090;;"></td><td style="text-align: center;background-color: #FAC090;;"></td><td style="text-align: center;background-color: #FAC090;;"></td><td style="text-align: center;background-color: #FAC090;;"></td><td style="text-align: center;background-color: #FAC090;;"></td><td style="text-align: center;background-color: #FAC090;;"></td><td style="text-align: center;background-color: #FAC090;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;background-color: #E6B9B8;;">7</td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #E6B9B8;;">1</td><td style="text-align: center;background-color: #E6B9B8;;">2</td><td style="text-align: center;background-color: #E6B9B8;;">4</td><td style="text-align: center;background-color: #E6B9B8;;">6</td><td style="text-align: center;background-color: #E6B9B8;;">7</td><td style="text-align: center;background-color: #E6B9B8;;">8</td><td style="text-align: center;background-color: #E6B9B8;;">18</td><td style="text-align: center;background-color: #FF0000;;">69</td><td style="text-align: center;background-color: #E6B9B8;;">100</td><td style="text-align: center;background-color: #E6B9B8;;">100</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;background-color: #92D050;;">VERDADEIRO</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;background-color: #E6B9B8;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #E6B9B8;;">1</td><td style="text-align: center;background-color: #E6B9B8;;">2</td><td style="text-align: center;background-color: #FF0000;;">3</td><td style="text-align: center;background-color: #E6B9B8;;">5</td><td style="text-align: center;background-color: #E6B9B8;;">7</td><td style="text-align: center;background-color: #E6B9B8;;">8</td><td style="text-align: center;background-color: #E6B9B8;;">9</td><td style="text-align: center;background-color: #E6B9B8;;">19</td><td style="text-align: center;background-color: #E6B9B8;;">101</td><td style="text-align: center;background-color: #E6B9B8;;">101</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;background-color: #92D050;;">VERDADEIRO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;background-color: #E6B9B8;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #E6B9B8;;">1</td><td style="text-align: center;background-color: #E6B9B8;;">2</td><td style="text-align: center;background-color: #FF0000;;">3</td><td style="text-align: center;background-color: #E6B9B8;;">6</td><td style="text-align: center;background-color: #E6B9B8;;">8</td><td style="text-align: center;background-color: #E6B9B8;;">9</td><td style="text-align: center;background-color: #E6B9B8;;">10</td><td style="text-align: center;background-color: #E6B9B8;;">20</td><td style="text-align: center;background-color: #E6B9B8;;">102</td><td style="text-align: center;background-color: #E6B9B8;;">102</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;background-color: #92D050;;">VERDADEIRO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;background-color: #E6B9B8;;">5</td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #E6B9B8;;">1</td><td style="text-align: center;background-color: #E6B9B8;;">2</td><td style="text-align: center;background-color: #E6B9B8;;">3</td><td style="text-align: center;background-color: #E6B9B8;;">7</td><td style="text-align: center;background-color: #E6B9B8;;">9</td><td style="text-align: center;background-color: #E6B9B8;;">10</td><td style="text-align: center;background-color: #E6B9B8;;">11</td><td style="text-align: center;background-color: #FF0000;;">21</td><td style="text-align: center;background-color: #E6B9B8;;">103</td><td style="text-align: center;background-color: #E6B9B8;;">103</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;background-color: #92D050;;">VERDADEIRO</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;background-color: #E6B9B8;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #E6B9B8;;">1</td><td style="text-align: center;background-color: #E6B9B8;;">2</td><td style="text-align: center;background-color: #E6B9B8;;">3</td><td style="text-align: center;background-color: #E6B9B8;;">4</td><td style="text-align: center;background-color: #E6B9B8;;">8</td><td style="text-align: center;background-color: #FF0000;;">10</td><td style="text-align: center;background-color: #E6B9B8;;">11</td><td style="text-align: center;background-color: #E6B9B8;;">12</td><td style="text-align: center;background-color: #E6B9B8;;">104</td><td style="text-align: center;background-color: #E6B9B8;;">104</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;background-color: #92D050;;">VERDADEIRO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;background-color: #E6B9B8;;">4</td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #E6B9B8;;">1</td><td style="text-align: center;background-color: #E6B9B8;;">2</td><td style="text-align: center;background-color: #E6B9B8;;">3</td><td style="text-align: center;background-color: #E6B9B8;;">4</td><td style="text-align: center;background-color: #E6B9B8;;">5</td><td style="text-align: center;background-color: #E6B9B8;;">9</td><td style="text-align: center;background-color: #FF0000;;">12</td><td style="text-align: center;background-color: #E6B9B8;;">13</td><td style="text-align: center;background-color: #E6B9B8;;">105</td><td style="text-align: center;background-color: #E6B9B8;;">105</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;background-color: #92D050;;">VERDADEIRO</td><td style="text-align: center;;">FALSO</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;background-color: #E6B9B8;;">4</td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #FF0000;;">1</td><td style="text-align: center;background-color: #E6B9B8;;">2</td><td style="text-align: center;background-color: #E6B9B8;;">3</td><td style="text-align: center;background-color: #E6B9B8;;">4</td><td style="text-align: center;background-color: #E6B9B8;;">5</td><td style="text-align: center;background-color: #E6B9B8;;">6</td><td style="text-align: center;background-color: #E6B9B8;;">10</td><td style="text-align: center;background-color: #E6B9B8;;">14</td><td style="text-align: center;background-color: #E6B9B8;;">106</td><td style="text-align: center;background-color: #E6B9B8;;">106</td><td style="text-align: center;background-color: #92D050;;">VERDADEIRO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;background-color: #E6B9B8;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #E6B9B8;;">1</td><td style="text-align: center;background-color: #E6B9B8;;">3</td><td style="text-align: center;background-color: #E6B9B8;;">4</td><td style="text-align: center;background-color: #FF0000;;">5</td><td style="text-align: center;background-color: #E6B9B8;;">6</td><td style="text-align: center;background-color: #E6B9B8;;">7</td><td style="text-align: center;background-color: #E6B9B8;;">11</td><td style="text-align: center;background-color: #E6B9B8;;">15</td><td style="text-align: center;background-color: #E6B9B8;;">107</td><td style="text-align: center;background-color: #E6B9B8;;">107</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;background-color: #92D050;;">VERDADEIRO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;background-color: #E6B9B8;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #FF0000;;">1</td><td style="text-align: center;background-color: #E6B9B8;;">2</td><td style="text-align: center;background-color: #E6B9B8;;">4</td><td style="text-align: center;background-color: #E6B9B8;;">5</td><td style="text-align: center;background-color: #E6B9B8;;">7</td><td style="text-align: center;background-color: #E6B9B8;;">8</td><td style="text-align: center;background-color: #E6B9B8;;">12</td><td style="text-align: center;background-color: #E6B9B8;;">16</td><td style="text-align: center;background-color: #E6B9B8;;">108</td><td style="text-align: center;background-color: #E6B9B8;;">108</td><td style="text-align: center;background-color: #92D050;;">VERDADEIRO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;background-color: #E6B9B8;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #E6B9B8;;">1</td><td style="text-align: center;background-color: #E6B9B8;;">3</td><td style="text-align: center;background-color: #E6B9B8;;">5</td><td style="text-align: center;background-color: #E6B9B8;;">6</td><td style="text-align: center;background-color: #FF0000;;">8</td><td style="text-align: center;background-color: #E6B9B8;;">9</td><td style="text-align: center;background-color: #E6B9B8;;">13</td><td style="text-align: center;background-color: #E6B9B8;;">17</td><td style="text-align: center;background-color: #E6B9B8;;">109</td><td style="text-align: center;background-color: #E6B9B8;;">109</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;background-color: #92D050;;">VERDADEIRO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;background-color: #E6B9B8;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #E6B9B8;;">1</td><td style="text-align: center;background-color: #FF0000;;">2</td><td style="text-align: center;background-color: #E6B9B8;;">4</td><td style="text-align: center;background-color: #E6B9B8;;">6</td><td style="text-align: center;background-color: #E6B9B8;;">7</td><td style="text-align: center;background-color: #E6B9B8;;">10</td><td style="text-align: center;background-color: #E6B9B8;;">14</td><td style="text-align: center;background-color: #E6B9B8;;">18</td><td style="text-align: center;background-color: #E6B9B8;;">110</td><td style="text-align: center;background-color: #E6B9B8;;">110</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;background-color: #92D050;;">VERDADEIRO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">FALSO</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;">***</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">AI3</th><td style="text-align:left">=INDEX(<font color="Blue">$N3:$W3,,$A3+1</font>)=Y3</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">AJ3</th><td style="text-align:left">=INDEX(<font color="Blue">$N3:$W3,,$A3+1</font>)=Z3</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[serge]

Hi Mark,

Now I got it the reason why it wouldn't work is because you wrote the formula in your native language which work for your Excel from over there, transcript in English language now work perfectly.

Thank you for been persistent.

Check your PM box.

 

[markmzz]

Hi Mark,

Now I got it the reason why it wouldn't work is because you wrote the formula in your native language which work for your Excel from over there, transcript in English language now work perfectly.

Thank you for been persistent.

Check your PM box.

Hi Serge,

I’m sorry for wrote the formula in my native language (ÍNDICE and not INDEX) . I didn’t see the problem.

But in the post #14 the formula is in English.

And thanks for the feedback.

Markmzz