Conditional formatting

[deanl33069]

HI all
I have several large numbers
019332961003232531020025441319041221179549530
19332961003232531020021401319041221179549570
1933296100323253102002236131904122117954954
0193329610032325310200235813190411211795387001
933296100323253102002280131904112117953858
01933296100323253102002122131904122117954955
01933296100323253102002324131904122117954961
019332961003232531020021261319041221179549390193
32961003232531020021361319041221179549450
1933296100323253102002136131904122117954956
01933296100323253102002126131904112117953898
019332961003232531020024001319042321179625220
1933296100323253102002230131904112117953904
01933296100323253102002410131904112117953871

that need to have conditional formatting applied however when i do this they all come up as duplicates when they are not.
any thoughts?>??

 

[Aryatect]

Hi,

Tried to post your numbers with custom number format "0" and also as text, the conditional formatting for duplicates seemed to work in the same fashion. However, when posted as numbers, these were rounded up. I don't know how these numbers are to be used for any mathematical operations, if not then posting as text might work:

Screenshot:

https://drive.google.com/open?id=1DZhlGyOGDBGoSl6USiB83esfM34Q00_-

	A	B	C	D
Number (custom format "0")	Text
19332961003232500000000000000000000000000000	019332961003232531020025441319041221179549530
19332961003232500000000000000000000000000000	19332961003232531020021401319041221179549570
1933296100323250000000000000000000000000000	1933296100323253102002236131904122117954954
193329610032325000000000000000000000000000000	0193329610032325310200235813190411211795387001
933296100323253000000000000000000000000000	933296100323253102002280131904112117953858
1933296100323250000000000000000000000000000	01933296100323253102002122131904122117954955
1933296100323250000000000000000000000000000	01933296100323253102002324131904122117954961
19332961003232500000000000000000000000000000000	019332961003232531020021261319041221179549390193
32961003232531000000000000000000000000000	32961003232531020021361319041221179549450
1933296100323250000000000000000000000000000	1933296100323253102002136131904122117954956
1933296100323250000000000000000000000000000	01933296100323253102002126131904112117953898
19332961003232500000000000000000000000000000	019332961003232531020024001319042321179625220
1933296100323250000000000000000000000000000	1933296100323253102002230131904112117953904
1933296100323250000000000000000000000000000	01933296100323253102002410131904112117953871

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]A1[/TH]
[TD="align: left"]Number (custom format "0")[/TD]
[/TR]
[TR]
[TH]B1[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]C1[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]D1[/TH]
[TD="align: left"]Text[/TD]
[/TR]
[TR]
[TH]A2[/TH]
[TD="align: left"]1.93329610032325E+43[/TD]
[/TR]
[TR]
[TH]B2[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]C2[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]D2[/TH]
[TD="align: left"]019332961003232531020025441319041221179549530[/TD]
[/TR]
[TR]
[TH]A3[/TH]
[TD="align: left"]1.93329610032325E+43[/TD]
[/TR]
[TR]
[TH]B3[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]C3[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]D3[/TH]
[TD="align: left"]19332961003232531020021401319041221179549570[/TD]
[/TR]
[TR]
[TH]A4[/TH]
[TD="align: left"]1.93329610032325E+42[/TD]
[/TR]
[TR]
[TH]B4[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]C4[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]D4[/TH]
[TD="align: left"]1933296100323253102002236131904122117954954[/TD]
[/TR]
[TR]
[TH]A5[/TH]
[TD="align: left"]1.93329610032325E+44[/TD]
[/TR]
[TR]
[TH]B5[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]C5[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]D5[/TH]
[TD="align: left"]0193329610032325310200235813190411211795387001[/TD]
[/TR]
[TR]
[TH]A6[/TH]
[TD="align: left"]9.33296100323253E+41[/TD]
[/TR]
[TR]
[TH]B6[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]C6[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]D6[/TH]
[TD="align: left"]933296100323253102002280131904112117953858[/TD]
[/TR]
[TR]
[TH]A7[/TH]
[TD="align: left"]1.93329610032325E+42[/TD]
[/TR]
[TR]
[TH]B7[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]C7[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]D7[/TH]
[TD="align: left"]01933296100323253102002122131904122117954955[/TD]
[/TR]
[TR]
[TH]A8[/TH]
[TD="align: left"]1.93329610032325E+42[/TD]
[/TR]
[TR]
[TH]B8[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]C8[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]D8[/TH]
[TD="align: left"]01933296100323253102002324131904122117954961[/TD]
[/TR]
[TR]
[TH]A9[/TH]
[TD="align: left"]1.93329610032325E+46[/TD]
[/TR]
[TR]
[TH]B9[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]C9[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]D9[/TH]
[TD="align: left"]019332961003232531020021261319041221179549390193[/TD]
[/TR]
[TR]
[TH]A10[/TH]
[TD="align: left"]3.2961003232531E+40[/TD]
[/TR]
[TR]
[TH]B10[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]C10[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]D10[/TH]
[TD="align: left"]32961003232531020021361319041221179549450[/TD]
[/TR]
[TR]
[TH]A11[/TH]
[TD="align: left"]1.93329610032325E+42[/TD]
[/TR]
[TR]
[TH]B11[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]C11[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]D11[/TH]
[TD="align: left"]1933296100323253102002136131904122117954956[/TD]
[/TR]
[TR]
[TH]A12[/TH]
[TD="align: left"]1.93329610032325E+42[/TD]
[/TR]
[TR]
[TH]B12[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]C12[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]D12[/TH]
[TD="align: left"]01933296100323253102002126131904112117953898[/TD]
[/TR]
[TR]
[TH]A13[/TH]
[TD="align: left"]1.93329610032325E+43[/TD]
[/TR]
[TR]
[TH]B13[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]C13[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]D13[/TH]
[TD="align: left"]019332961003232531020024001319042321179625220[/TD]
[/TR]
[TR]
[TH]A14[/TH]
[TD="align: left"]1.93329610032325E+42[/TD]
[/TR]
[TR]
[TH]B14[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]C14[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]D14[/TH]
[TD="align: left"]1933296100323253102002230131904112117953904[/TD]
[/TR]
[TR]
[TH]A15[/TH]
[TD="align: left"]1.93329610032325E+42[/TD]
[/TR]
[TR]
[TH]B15[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]C15[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH]D15[/TH]
[TD="align: left"]01933296100323253102002410131904112117953871[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[deanl33069]

ty
no matter how i format the cell they all come back as duplicates .......
i formatted the cells first and after but it didnt work.
SAD...

 

[Aryatect]

ty
no matter how i format the cell they all come back as duplicates .......
i formatted the cells first and after but it didnt work.
SAD...

Other way can be that use sort on the columnand then use a formula for conditional formatting =A1=A2.

 

[DanteAmor]

Apparently if you only have numbers valid up to 42 digits. But if in a column auxiliary to the number you add a letter at the end and in the auxiliary column you apply the duplicate format, then it works.


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:422.02px;" /><col style="width:389.7px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#92d050; text-align:right; ">019332961003232531020025441319041221179549530</td><td >019332961003232531020025441319041221179549530a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#92d050; text-align:right; ">19332961003232531020021401319041221179549570</td><td >19332961003232531020021401319041221179549570a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#92d050; text-align:right; ">1933296100323253102002236131904122117954954</td><td >1933296100323253102002236131904122117954954a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#d0d7e5; text-align:right; ">0193329610032325310200235813190411211795387001</td><td >0193329610032325310200235813190411211795387001a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="background-color:#d0d7e5; text-align:right; ">933296100323253102002280131904112117953858</td><td >933296100323253102002280131904112117953858a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#92d050; text-align:right; ">01933296100323253102002122131904122117954955</td><td >01933296100323253102002122131904122117954955a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="background-color:#92d050; text-align:right; ">01933296100323253102002324131904122117954961</td><td >01933296100323253102002324131904122117954961a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="background-color:#d0d7e5; text-align:right; ">019332961003232531020021261319041221179549390193</td><td >019332961003232531020021261319041221179549390193a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="background-color:#d0d7e5; text-align:right; ">32961003232531020021361319041221179549450</td><td >32961003232531020021361319041221179549450a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="background-color:#92d050; text-align:right; ">1933296100323253102002136131904122117954956</td><td >1933296100323253102002136131904122117954956a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="background-color:#92d050; text-align:right; ">01933296100323253102002126131904112117953898</td><td >01933296100323253102002126131904112117953898a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="background-color:#ffc7ce; text-align:right; ">01933296100323253102002410131904112117953871</td><td style="background-color:#ffc7ce; ">01933296100323253102002410131904112117953871a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="background-color:#92d050; text-align:right; ">1933296100323253102002230131904112117953904</td><td >1933296100323253102002230131904112117953904a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="background-color:#ffc7ce; text-align:right; ">01933296100323253102002410131904112117953871</td><td style="background-color:#ffc7ce; ">01933296100323253102002410131904112117953871a</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B1</td><td >=A1&"a"</td></tr></table></td></tr></table>